云顶集团用户登录「官方网站」

热门关键词: 云顶集团用户登录,云顶集团登录网站
来自 关于我们 2020-01-05 19:21 的文章
当前位置: 云顶集团用户登录 > 关于我们 > 正文

下面几个语句很值得玩味,要根据单据的不同类

参照了瞬间那篇小说: , 谢谢最先的著我

 

有八个表,分别存放了【操作员】和【单据】,要依附单据的不及品种来分类聚焦(出售单、发售退货单,笔数和金额卡塔尔,并且出示在同等张表里,不想用做五回询问再统生机勃勃的点子,研商了生龙活虎晃,终于解决:

它山之石能够攻玉,那生龙活虎篇是读外人的博客后写下的,不是自始自终的转发,到场了温馨的拆解解析进程和排练。sql语句可以消除广大的繁缛专门的学问,制止过多的档案的次序代码,上面多少个语句很值得赏识。

d_employee表

  

图片 1

1. 已经驾驭原表
year salary
2000 1000
2001 2000
2002 3000
2003 4000
怎么查询的到下边包车型地铁结果,正是积存薪水
year salary
2000 1000
2001 3000
2002 6000
2003 10000

d_bilndx表

思路:这些要求三个表交叉查询获得当二零生机勃勃三年的全数过过去,然后再对过往年张开联谊。代码如下:

图片 2

 

 

create table #salary(years int ,salary int )
insert into #salary values
(2000, 1000),
(2001, 2000),
(2002, 3000),
(2003, 4000)

select  b.years,SUM(a.salary)
from #salary a,#salary b
where a.years<=b.years
group by b.years
order by b.years

代码如下:

  

select  b.inputid as 开单员编号, 
        e.fullname as 开单员, 

        isnull( ( select count(*)
          from d_bilndx
          where draft=3 and biltype=12 and d_bilndx.inputid=e.id
        ), 0) as '销售开单笔数',

        isnull( ( select sum(d_bilndx.amount)
          from d_bilndx
          where draft=3 and biltype=12 and d_bilndx.inputid=e.id
        ), 0) as '销售开单金额',

        isnull( ( select count(*)
          from d_bilndx
          where draft=3 and biltype=13 and d_bilndx.inputid=e.id
        ), 0) as '销售退单笔数',

        isnull( ( select sum(d_bilndx.amount)
          from d_bilndx
          where draft=3 and biltype=13 and d_bilndx.inputid=e.id
        ), 0) as '销售退单金额',

        count(b.biltype) as 开单总笔数,
        sum(b.Amount) as 开单金额

from d_bilndx as b
left join d_employee as e 
on b.inputid=e.id 
where b.draft=3 and ( b.biltype=12 or b.biltype=13 )
group by b.inputid, e.fullname, e.id

 

 

再有意气风发种办法是使用子查询,第一列是年,第二列是持有小于等于第一列那一年的薪俸总量,也比较直接,代码如下:

赢得结果:

 

 

 

图片 3

select 
s1.years as years,
(select sum(s2.salary) from #salary s2 where s2.years<=s1.years) as salary 
from #salary s1

 

  

补记:以上代码有一个难题,便是若无相符条件的左券,查到的结果为空,而我们大概希望,查不到相符条件的记录,相关字段要体现为0(况且按天来总括),改写代码如下:

 

 

 

select  e1.id as ePersonCode, e1.FullName as eFullName, 
        isnull(Bill_Sale_NUm, 0) as Bill_Sale_Num, 
        isnull(Bill_Sale_Amount, 0) as Bill_Sale_Amount, 
        isnull(Bill_SaleReturn_Num, 0) as Bill_SaleReturn_Num, 
        isnull(Bill_SaleReturn_Amount, 0) as Bill_SaleReturn_Amount
from d_employee as e1
left join (

        select  b.inputid as ePersonCode, 
                e.fullname as eFullName, 

                isnull( ( select count(*)
                  from d_bilndx
                  where biltype=12 and d_bilndx.inputid=e.id and d_bilndx.date>='2018-06-03' and d_bilndx.date<='2018-06-03'
                ), 0) as Bill_Sale_Num,

                isnull( ( select sum(d_bilndx.amount)
                  from d_bilndx
                  where biltype=12 and d_bilndx.inputid=e.id and d_bilndx.date>='2018-06-03' and d_bilndx.date<='2018-06-03'
                ), 0) as Bill_Sale_Amount,

                isnull( ( select count(*)
                  from d_bilndx
                  where biltype=13 and d_bilndx.inputid=e.id and d_bilndx.date>='2018-06-03' and d_bilndx.date<='2018-06-03'
                ), 0) as Bill_SaleReturn_Num,

                isnull( ( select sum(d_bilndx.amount)
                  from d_bilndx
                  where biltype=13 and d_bilndx.inputid=e.id and d_bilndx.date>='2018-06-03' and d_bilndx.date<='2018-06-03'
                ), 0) as Bill_SaleReturn_Amount,

                count(b.biltype) as Bill_Total_Num

        from d_employee as e 
        left join d_bilndx as b
        on b.inputid=e.id 
        where (b.draft=3 or b.draft=2) and ( b.biltype=12 or b.biltype=13 ) and  b.date>='2018-06-03' and b.date<='2018-06-03'
        group by b.inputid, e.fullname, e.id


) as t1
on e1.id = t1.ePersonCode
where e1.id<>'00000'
order by ePersonCode asc

2. 现行反革命我们若是独有一个table,名称为pages,有三个字段,id, url,title,body。里面储存了过多网页,网页的url地址,title和网页的内容,然后你用三个sql查询将url相配的排在最前, title相称的附带,body相称最后,未有其余字段相配的,不回来。

 

 

 

思路:做过模糊寻找对那几个相应很熟知的,能够动用union all依次向三个有的时候表中增加记录。这里运用order by和charindex来是促成,代码如下:

 

 

 

create table #page(id int, url varchar(100),title varchar(100), body varchar(100))
insert into #page values
(1,null,'abcde','abcde'),
(2,null,'abcde',null),
(3,'abcde','e',null)

select *
from #page 
where url like '%e%' or title like '%e%' or body like '%e%'
order by 
case when (charindex('e', url)>0) then 1 else 0 end desc, 
case when (charindex('e', title)>0) then 1 else 0 end desc, 
case when (charindex('e', body)>0) then 1 else 0 end desc

收获结果如下:

  

图片 4

风华正茂经现身三次就能排在前边,这种情形假若两行都现身就能够相比下三个字段,依此类推。

 

再有大器晚成种达成,相像于记分牌的沉凝,如下:

select a.[id],sum(a.mark) as summark  from
(
select #page.*,10 as mark from #page where #page.[url] like '%b%'
union
select #page.*,5 as mark from #page where #page.[title] like '%b%'
union
select #page.*,1 as mark from #page where #page.[body] like '%b%'
) as a  group by id order by summark desc

    

 

3. 表内容:
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

若是要生成下列结果, 该怎么写sql语句?

                       胜负
2005-05-09  2    2
2005-05-10  1    2

思路:首先要有group by 时间,然后是应用sum总结胜负的个数。代码如下:

 

create table #scores(dates varchar(10),score varchar(2))
insert into #scores values
('2005-05-09', '胜'),
('2005-05-09', '胜'),
('2005-05-09', '负'),
('2005-05-09', '负'),
('2005-05-10', '胜'),
('2005-05-10', '负'),
('2005-05-10', '负')

select a.dates as [比赛时间],
SUM(case a.score when '胜' then 1 else 0 end) as [胜],
SUM(case a.score when '负' then 1 else 0 end) as [负]
from #scores a
group by a.dates

  

 

还会有生机勃勃种艺术是使用子查询,先用多少个子查询获得那一个日子中的胜负常数,然后连接查询,代码如下:

 

 

select 
t1.dates as [比赛时间],
t1.score as [胜],
t2.score as [负]
from
(select a.dates as dates, COUNT(1) as score from #scores a where a.score='胜'  group by a.dates) t1 inner join
(select a.dates as dates, COUNT(1) as score from #scores a where a.score='负'  group by a.dates) t2 on t1.dates=t2.dates

 

  

  

 

 

4. 表中有A B C三列,用SQL语句落成:当A列大于B列时选取A列否则选拔B列,当B列大于C列时选拔B列不然选取C列

 

思路:那个字面意思非常粗大略了,便是两岸选其生龙活虎,使用case即可完结,代码如下:

 

create table #table3(A int, B int ,C int)
insert into #table3 values
(2,1,3),
(4,2,5)

select 
case when A>B then A else B end as AB,
case when B>C then B else C end as BC
from #table3

  

  

 

5. 请用多个sql语句得出结果

从table1,table2中抽出如table3所列格式数据,注意提供的数据及结果不可相信,只是作为七个格式向我们请教。

table1

月份          部门业绩

一月份      01      10

一月份      02      10

一月份      03      5

二月份      02      8

二月份      04      9

三月份      03      8

 

table2

单位     部门名称

01      本国事务风流倜傥部

02      本国事务二部

03      国内业务三部

04      国际业务部

 

table3 (result)

单位单位名称  八月份      五月份      四月份

  01  国内专业风度翩翩部    10        null      null

  02   国内专门的学业二部   10         8        null

  03   国内业务三部   null       5        8

  04   国际业务部   null      null      9

思路:又是行列调换,不过那么些略带复杂一点代码如下:

本文由云顶集团用户登录发布于关于我们,转载请注明出处:下面几个语句很值得玩味,要根据单据的不同类

关键词: