求一查询语句?

Question

求一查询语句
表1
部门   产品    日期
001    1001   2009-01-06
001    1001   2009-01-05
001    1001   2009-01-06
001    1001   2009-01-05
001    1001   2009-01-06
001    1002   2009-01-08
001    1002   2009-01-08
001    1002   2009-01-31
001    1003   2009-01-10
001    1005   2009-01-07
001    1006   2009-02-02
002    1001   2009-01-06
002    1001   2009-01-05
002    1001   2009-01-06
002    1001   2009-01-05
002    1002   2009-01-06
002    1002   2009-01-08
002    1002   2009-01-08
002    1003   2009-01-31
002    1003   2009-01-10
002    1005   2009-01-07
002    1006   2009-02-02表2
产品
1001
1002
1003
1005现在想得到各部门同一产品记录数小于等于2,大于2小于5,大于等己于5分别是多少?表1中产品号在表2存在时又分别是多少?
根据测试数据结果应如下:(在2009-01-01至2009-02-28时间段内)
部门   <=2    >2 <5   >=5    <=2(表二)    >2 <5 (表二)   >=5(表二)
001    3       1      1         2            1            1
002    3       2      0         2            2            0
另外前三项和后三项统计时可不用left join连接吗?因为表1非常大(表二小)怕影响效率.

Answer 1

部门    <=2  >2  <5   >=5   <=2(表二)  >2  <5 (表二)   >=5(表二)
001  3     1    1     2      1      1没看懂，这个 3 是怎么来的？

Answer 2

001    1003  2009-01-10 
001    1005  2009-01-07 
001    1006  2009-02-02 
<=2中为3
001    1001  2009-01-06 
001    1001  2009-01-05 
001    1001  2009-01-06 
001    1001  2009-01-05 
001    1001  2009-01-06 
>=5中为1
001    1002  2009-01-08 
001    1002  2009-01-08 
001    1002  2009-01-31 
>2 <5中为1

Answer 3

1> select 部门,
2>      sum(case when cnt1<=2 then 1 end) as [<=2],
3>      sum(case when cnt1>2 and cnt1<5  then 1 end) as [>2  <5],
4>      sum(case when cnt1>=5 then 1 end) as [>=5],
5>      sum(case when cnt2<=2 then 1 end) as [<=2(表二)],
6>      sum(case when cnt2>2 and cnt2<5  then 1 end) as [>2  <5(表二)],
7>      sum(case when cnt2>=5 then 1 end) as [>=5(表二)]
8> from (
9>      select 表1.部门,表1.产品,count(表1.产品) as cnt1,count(表2.产品) as cnt2
10>     from 表1 left join 表2 on 表1.产品=表2.产品
11>     where 日期 between '2009-01-01' and '2009-02-28'
12>     group by 表1.部门,表1.产品
13> )t
14> group by 部门
15> go
部门 |<=2        |>2  <5     |>=5        |<=2(表二)  |>2  <5(表二) |>=5(表二)
  ---|-----------|-----------|-----------|-----------|-----------|-----------
  001|          3|          1|          1|          3|          1|          1
  002|          3|          2|       NULL|          3|          2|       NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
1>

Answer 4

ACMAIN_CHM谢谢你
但你的|<=2(表二)  |>2  <5(表二) |>=5(表二)结果不对因为1006在表2中没有故统计时应不含,正确应时
001       2            1            1 
002       2            2            0 

Answer 5

1> select 部门,
2>      sum(case when cnt1<=2 then 1 end) as [<=2],
3>      sum(case when cnt1>2 and cnt1<5  then 1 end) as [>2  <5],
4>      sum(case when cnt1>=5 then 1 end) as [>=5],
5>      sum(case when cnt2<=2 and cnt2>0 then 1 end) as [<=2(表二)],
6>      sum(case when cnt2>2 and cnt2<5  then 1 end) as [>2  <5(表二)],
7>      sum(case when cnt2>=5 then 1 end) as [>=5(表二)]
8> from (
9>      select 表1.部门,表1.产品,count(表1.产品) as cnt1,count(表2.产品) as cnt210>
11>      from 表1 left join 表2 on 表1.产品=表2.产品
12>      where 日期 between '2009-01-01' and '2009-02-28'
13>      group by 表1.部门,表1.产品
14>  )t
15> group by 部门
16> go
部门 |<=2        |>2  <5     |>=5        |<=2(表二)    |>2  <5(表二) |>=5(表二)---|-----------|-----------|-----------|-----------|-----------|-----------
001|          3|          1|          1|          2|          1|          1
002|          3|          2|       NULL|          2|          2|       NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
1>

Answer 6

1> select 部门,
2>      sum(case when cnt1<=2 then 1 else 0 end) as [<=2],
3>      sum(case when cnt1>2 and cnt1<5  then 1 else 0 end) as [>2  <5],
4>      sum(case when cnt1>=5 then 1 else 0 end) as [>=5],
5>      sum(case when cnt2<=2 and cnt2>0 then 1 else 0 end) as [<=2(表二)],
6>      sum(case when cnt2>2 and cnt2<5  then 1 else 0 end) as [>2  <5(表二)],
7>      sum(case when cnt2>=5 then 1 else 0 end) as [>=5(表二)]
8> from (
9>      select 表1.部门,表1.产品,count(表1.产品) as cnt1,count(表2.产品) as cnt2
10>
11>      from 表1 left join 表2 on 表1.产品=表2.产品
12>      where 日期 between '2009-01-01' and '2009-02-28'
13>      group by 表1.部门,表1.产品
14>  )t
15> group by 部门
16> go
部门 |<=2        |>2  <5     |>=5        |<=2(表二)    |>2  <5(表二) |>=5(表二)---|-----------|-----------|-----------|-----------|-----------|-----------
001|          3|          1|          1|          2|          1|          1
002|          3|          2|          0|          2|          2|          0
1>

Answer 7

顶7楼的，像这样的题要用sum（case...when...then...else...end)

Answer 8

ACMAIN_CHM谢谢你
我试过你的方法和用(selcet sum（case...when...then...else...end)
from
where)
left join
(selcet sum（case...when...then...else...end)
from
where and 产品 in (select 产品 from 表2)
效率差不多.

Answer 9

ACMAIN_CHM再次谢谢你!
今天刚在单位试过,用你的
select 部门,
      sum(case when cnt1<=2 then 1 else 0 end) as [<=2],
      sum(case when cnt1>2 and cnt1<5  then 1 else 0 end) as [>2  <5],
      sum(case when cnt1>=5 then 1 else 0 end) as [>=5],
      sum(case when cnt2<=2 and cnt2>0 then 1 else 0 end) as [<=2(表二)],
      sum(case when cnt2>2 and cnt2<5  then 1 else 0 end) as [>2  <5(表二)],
      sum(case when cnt2>=5 then 1 else 0 end) as [>=5(表二)]
from (
      select 表1.部门,表1.产品,count(表1.产品) as cnt1,count(表2.产品) as cnt2
      from 表1 left join 表2 on 表1.产品=表2.产品
      where 日期 between '2009-01-01' and '2009-02-28'
      group by 表1.部门,表1.产品
      )t
group by 部门和用
(select 部门,
      sum(case when cnt1<=2 then 1 else 0 end) as [<=2],
      sum(case when cnt1>2 and cnt1<5  then 1 else 0 end) as [>2  <5],
      sum(case when cnt1>=5 then 1 else 0 end) as [>=5]
from (
      select 表1.部门,表1.产品,count(表1.产品) as cnt1
      from 表1
      where 日期 between '2009-01-01' and '2009-02-28'
      group by 表1.部门,表1.产品
      )t
group by 部门)
left join
(select 部门,
      sum(case when cnt1<=2 then 1 else 0 end) as [<=2],
      sum(case when cnt1>2 and cnt1<5  then 1 else 0 end) as [>2  <5],
      sum(case when cnt1>=5 then 1 else 0 end) as [>=5]
from (
      select 表1.部门,表1.产品,count(表1.产品) as cnt1
      from 表1
      where 日期 between '2009-01-01' and '2009-02-28' and 表1.产品 in (select 产品            from 表2)
      group by 表1.部门,表1.产品
      )t
group by 部门)
查询时间基本相同,由于表1记录数近1000万条(表2则很少约200条记录),在这也顺便请教各位高手还有没有其它更好的办法.

Answer 10

--> 测试数据:
create table tb (pm varchar(4),cp int,[datetime] datetime)
insert into tb
select '001',1001,'2009-01-06' union all
select '001',1001,'2009-01-05' union all 
select '001',1001,'2009-01-06' union all 
select '001',1001,'2009-01-05' union all 
select '001',1001,'2009-01-06' union all 
select '001',1002,'2009-01-08' union all 
select '001',1002,'2009-01-08' union all 
select '001',1002,'2009-01-31' union all 
select '001',1003,'2009-01-10' union all 
select '001',1005,'2009-01-07' union all 
select '001',1006,'2009-02-02' union all 
select '002',1001,'2009-01-06' union all 
select '002',1001,'2009-01-05' union all 
select '002',1001,'2009-01-06' union all 
select '002',1001,'2009-01-05' union all 
select '002',1002,'2009-01-06' union all 
select '002',1002,'2009-01-08' union all 
select '002',1002,'2009-01-08' union all 
select '002',1003,'2009-01-31' union all 
select '002',1003,'2009-01-10' union all  
select '002',1005,'2009-01-07' union all 
select '002',1006,'2009-02-02'
create table tb1 (cp int)
insert into tb1
select 1001 union all
select 1002 union all
select 1003 union all
select 1005 ----------------------------------
select pm 部门, 
      sum(case when cnt1 <=2 then 1 else 0 end) as [ <=2], 
      sum(case when cnt1>2 and cnt1 <5  then 1 else 0 end) as [>2  <5], 
      sum(case when cnt1>=5 then 1 else 0 end) as [>=5], 
      sum(case when cnt2 <=2 and cnt2>0 then 1 else 0 end) as [ <=2(表二)], 
      sum(case when cnt2>2 and cnt2 <5  then 1 else 0 end) as [>2  <5(表二)], 
      sum(case when cnt2>=5 then 1 else 0 end) as [>=5(表二)] 
from ( 
      select pm,tb.cp,count(tb.cp) as cnt1,count(tb1.cp) as cnt2 
      from tb left join tb1 on tb.cp=tb1.cp 
      where datetime between '2009-01-01' and '2009-02-28' 
      group by pm,tb.cp 
      )t 
group by pm drop table tb
drop table tb1部门  <=2    >2 <5  >=5    <=2(表二)    >2 <5 (表二)  >=5(表二) 
001    3      1      1        2            1            1 
002    3      2      0        2            2            0