22,209
社区成员
发帖
与我相关
我的任务
分享Sql两张表，统计的问题。很急！求各位赐教哦
表1：
设备编号 业务类型
10001 10
10001 11
10001 12
10002 11
10003 10
10003 11
表2：
设备编号 业务类型
10001 10
10001 10
10001 11
10002 11
10003 10
10003 11
10004 10
如何查出如下数据：（就是以设备分组查出不同业务的数量）
设备编号 1的10业务 1的11业务 1的12业务 2的10业务 2的11业务 1总 2总
10001 1 1 1 2 1 3 3
10002 0 1 0 0 1 1 1
10003 1 1 0 1 1 2 2
10004 0 0 0 1 0 0 1
总 2 3 1 4 3 6 7
设备编号 业务类型
10001 10
10001 11
10001 12
10002 11
10003 10
10003 11
表2：
设备编号 业务类型
10001 10
10001 10
10001 11
10002 11
10003 10
10003 11
10004 10
如何查出如下数据：（就是以设备分组查出不同业务的数量）
设备编号 1的10业务 1的11业务 1的12业务 2的10业务 2的11业务 1总 2总
10001 1 1 1 2 1 3 3
10002 0 1 0 0 1 1 1
10003 1 1 0 1 1 2 2
10004 0 0 0 1 0 0 1
总 2 3 1 4 3 6 7
...全文
请发表友善的回复…
发表回复
emailqjc 2007-07-11
- 打赏
- 举报
我用select '','老妈火锅粉','洁美凉粉','倍佳美熟食','夏记串串','巴西烧烤','北京丁大毛冰糖葫芦','查渣面','欧意咖啡','马嫂连锅','喜来锅魁','韩式铁板烧','兰州拉面','原味小吃','黄艳','赛伦西餐厅','好味得快餐','女皇蒸凉面'
union all
select right(xsrq,5), cast( sum(case when b.sjbh='000393' then zxssze else 0 end ) as varchar(10)) 老妈火锅粉 , cast( sum(case when b.sjbh='000887' then zxssze else 0 end ) as varchar(10)) 洁美凉粉 , cast( sum(case when b.sjbh='000888' then zxssze else 0 end ) as varchar(10)) 倍佳美熟食 , cast( sum(case when b.sjbh='000889' then zxssze else 0 end ) as varchar(10)) 夏记串串 , cast( sum(case when b.sjbh='000890' then zxssze else 0 end ) as varchar(10)) 巴西烧烤 , cast( sum(case when b.sjbh='000891' then zxssze else 0 end ) as varchar(10)) 北京丁大毛冰糖葫芦 , cast( sum(case when b.sjbh='000892' then zxssze else 0 end ) as varchar(10)) 查渣面 , cast( sum(case when b.sjbh='000899' then zxssze else 0 end ) as varchar(10)) 欧意咖啡 , cast( sum(case when b.sjbh='000900' then zxssze else 0 end ) as varchar(10)) 马嫂连锅 , cast( sum(case when b.sjbh='000901' then zxssze else 0 end ) as varchar(10)) 喜来锅魁 , cast( sum(case when b.sjbh='000902' then zxssze else 0 end ) as varchar(10)) 韩式铁板烧 , cast( sum(case when b.sjbh='000903' then zxssze else 0 end ) as varchar(10)) 兰州拉面 , cast( sum(case when b.sjbh='000905' then zxssze else 0 end ) as varchar(10)) 原味小吃 , cast( sum(case when b.sjbh='100333' then zxssze else 0 end ) as varchar(10)) 黄艳 , cast( sum(case when b.sjbh='100339' then zxssze else 0 end ) as varchar(10)) 赛伦西餐厅 , cast( sum(case when b.sjbh='100340' then zxssze else 0 end ) as varchar(10)) 好味得快餐 , cast( sum(case when b.sjbh='100359' then zxssze else 0 end ) as varchar(10)) 女皇蒸凉面 , sum(case when xsrq=a.xsrq then zxssze else 0 end) 日小计 from uv_saledetail a,uv_spbaseinfo b,sj_sjhtxx c
where a.spbm=b.spbm and c.sjbh=b.sjbh and a.fdbh='01' and xsrq between '2007-06-01' and '2007-06-30'
and b.dlbmid='25' group by xsrq order by xsrq
go
select cast( sum(case when b.sjbh='000393' then zxssze else 0 end ) as varchar(10)) 老妈火锅粉 , cast( sum(case when b.sjbh='000887' then zxssze else 0 end ) as varchar(10)) 洁美凉粉 , cast( sum(case when b.sjbh='000888' then zxssze else 0 end ) as varchar(10)) 倍佳美熟食 , cast( sum(case when b.sjbh='000889' then zxssze else 0 end ) as varchar(10)) 夏记串串 , cast( sum(case when b.sjbh='000890' then zxssze else 0 end ) as varchar(10)) 巴西烧烤 , cast( sum(case when b.sjbh='000891' then zxssze else 0 end ) as varchar(10)) 北京丁大毛冰糖葫芦 , cast( sum(case when b.sjbh='000892' then zxssze else 0 end ) as varchar(10)) 查渣面 , cast( sum(case when b.sjbh='000899' then zxssze else 0 end ) as varchar(10)) 欧意咖啡 , cast( sum(case when b.sjbh='000900' then zxssze else 0 end ) as varchar(10)) 马嫂连锅 , cast( sum(case when b.sjbh='000901' then zxssze else 0 end ) as varchar(10)) 喜来锅魁 , cast( sum(case when b.sjbh='000902' then zxssze else 0 end ) as varchar(10)) 韩式铁板烧 , cast( sum(case when b.sjbh='000903' then zxssze else 0 end ) as varchar(10)) 兰州拉面 , cast( sum(case when b.sjbh='000905' then zxssze else 0 end ) as varchar(10)) 原味小吃 , cast( sum(case when b.sjbh='100333' then zxssze else 0 end ) as varchar(10)) 黄艳 , cast( sum(case when b.sjbh='100339' then zxssze else 0 end ) as varchar(10)) 赛伦西餐厅 , cast( sum(case when b.sjbh='100340' then zxssze else 0 end ) as varchar(10)) 好味得快餐 , cast( sum(case when b.sjbh='100359' then zxssze else 0 end ) as varchar(10)) 女皇蒸凉面 , sum(case when xsrq=a.xsrq then zxssze else 0 end)
from uv_saledetail a,uv_spbaseinfo b,sj_sjhtxx c
where a.spbm=b.spbm and c.sjbh=b.sjbh and a.fdbh='01' and xsrq between '2007-06-01' and '2007-06-30'
and b.dlbmid='25'
不能通过,请问大家有什么高招,
union all
select right(xsrq,5), cast( sum(case when b.sjbh='000393' then zxssze else 0 end ) as varchar(10)) 老妈火锅粉 , cast( sum(case when b.sjbh='000887' then zxssze else 0 end ) as varchar(10)) 洁美凉粉 , cast( sum(case when b.sjbh='000888' then zxssze else 0 end ) as varchar(10)) 倍佳美熟食 , cast( sum(case when b.sjbh='000889' then zxssze else 0 end ) as varchar(10)) 夏记串串 , cast( sum(case when b.sjbh='000890' then zxssze else 0 end ) as varchar(10)) 巴西烧烤 , cast( sum(case when b.sjbh='000891' then zxssze else 0 end ) as varchar(10)) 北京丁大毛冰糖葫芦 , cast( sum(case when b.sjbh='000892' then zxssze else 0 end ) as varchar(10)) 查渣面 , cast( sum(case when b.sjbh='000899' then zxssze else 0 end ) as varchar(10)) 欧意咖啡 , cast( sum(case when b.sjbh='000900' then zxssze else 0 end ) as varchar(10)) 马嫂连锅 , cast( sum(case when b.sjbh='000901' then zxssze else 0 end ) as varchar(10)) 喜来锅魁 , cast( sum(case when b.sjbh='000902' then zxssze else 0 end ) as varchar(10)) 韩式铁板烧 , cast( sum(case when b.sjbh='000903' then zxssze else 0 end ) as varchar(10)) 兰州拉面 , cast( sum(case when b.sjbh='000905' then zxssze else 0 end ) as varchar(10)) 原味小吃 , cast( sum(case when b.sjbh='100333' then zxssze else 0 end ) as varchar(10)) 黄艳 , cast( sum(case when b.sjbh='100339' then zxssze else 0 end ) as varchar(10)) 赛伦西餐厅 , cast( sum(case when b.sjbh='100340' then zxssze else 0 end ) as varchar(10)) 好味得快餐 , cast( sum(case when b.sjbh='100359' then zxssze else 0 end ) as varchar(10)) 女皇蒸凉面 , sum(case when xsrq=a.xsrq then zxssze else 0 end) 日小计 from uv_saledetail a,uv_spbaseinfo b,sj_sjhtxx c
where a.spbm=b.spbm and c.sjbh=b.sjbh and a.fdbh='01' and xsrq between '2007-06-01' and '2007-06-30'
and b.dlbmid='25' group by xsrq order by xsrq
go
select cast( sum(case when b.sjbh='000393' then zxssze else 0 end ) as varchar(10)) 老妈火锅粉 , cast( sum(case when b.sjbh='000887' then zxssze else 0 end ) as varchar(10)) 洁美凉粉 , cast( sum(case when b.sjbh='000888' then zxssze else 0 end ) as varchar(10)) 倍佳美熟食 , cast( sum(case when b.sjbh='000889' then zxssze else 0 end ) as varchar(10)) 夏记串串 , cast( sum(case when b.sjbh='000890' then zxssze else 0 end ) as varchar(10)) 巴西烧烤 , cast( sum(case when b.sjbh='000891' then zxssze else 0 end ) as varchar(10)) 北京丁大毛冰糖葫芦 , cast( sum(case when b.sjbh='000892' then zxssze else 0 end ) as varchar(10)) 查渣面 , cast( sum(case when b.sjbh='000899' then zxssze else 0 end ) as varchar(10)) 欧意咖啡 , cast( sum(case when b.sjbh='000900' then zxssze else 0 end ) as varchar(10)) 马嫂连锅 , cast( sum(case when b.sjbh='000901' then zxssze else 0 end ) as varchar(10)) 喜来锅魁 , cast( sum(case when b.sjbh='000902' then zxssze else 0 end ) as varchar(10)) 韩式铁板烧 , cast( sum(case when b.sjbh='000903' then zxssze else 0 end ) as varchar(10)) 兰州拉面 , cast( sum(case when b.sjbh='000905' then zxssze else 0 end ) as varchar(10)) 原味小吃 , cast( sum(case when b.sjbh='100333' then zxssze else 0 end ) as varchar(10)) 黄艳 , cast( sum(case when b.sjbh='100339' then zxssze else 0 end ) as varchar(10)) 赛伦西餐厅 , cast( sum(case when b.sjbh='100340' then zxssze else 0 end ) as varchar(10)) 好味得快餐 , cast( sum(case when b.sjbh='100359' then zxssze else 0 end ) as varchar(10)) 女皇蒸凉面 , sum(case when xsrq=a.xsrq then zxssze else 0 end)
from uv_saledetail a,uv_spbaseinfo b,sj_sjhtxx c
where a.spbm=b.spbm and c.sjbh=b.sjbh and a.fdbh='01' and xsrq between '2007-06-01' and '2007-06-30'
and b.dlbmid='25'
不能通过,请问大家有什么高招,
emailqjc 2007-07-11
- 打赏
- 举报
要得到如下效果:
日期 老妈火锅粉 洁美凉粉 倍佳美熟食 夏记串串 巴西烧烤 北京丁大毛冰糖葫芦 查渣面 欧意咖啡 马嫂连锅 喜来锅魁 韩式铁板烧 兰州拉面 原味小吃 黄艳 赛伦西餐厅 好味得快餐 女皇蒸凉面 日小计
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------
06-01 2309.50 247.50 1119.50 223.20 238.00 0.00 261.00 174.00 0.00 0.00 0.00 237.00 163.50 834.00 0.00 473.50 0.00 6280.7000
06-02 1643.00 218.00 663.50 130.60 172.00 10.00 211.00 83.00 0.00 0.00 0.00 174.00 156.50 658.00 28.00 484.50 0.00 4632.1000
06-03 1745.50 211.51 540.00 156.00 241.00 2.50 186.00 132.00 0.00 0.00 0.00 295.00 118.00 478.00 0.00 571.00 0.00 4676.5080
06-04 1014.50 109.00 283.00 39.30 83.00 5.00 137.00 79.00 0.00 0.00 0.00 178.00 53.00 282.00 0.00 326.50 0.00 2589.3000
06-05 962.01 116.49 208.99 60.30 143.50 0.00 106.50 33.00 0.00 0.00 0.00 138.00 99.50 187.01 0.00 266.50 0.00 2321.7842
06-06 1151.50 108.00 334.00 100.50 167.50 0.00 202.00 117.00 0.00 0.00 0.00 223.00 89.00 231.00 25.00 259.00 0.00 3007.5000
06-07 1145.50 122.00 278.50 117.30 132.20 0.00 134.50 24.00 0.00 0.00 0.00 206.00 87.00 271.01 31.00 357.00 0.00 2906.0100
06-08 1181.50 63.00 326.00 192.00 189.00 2.50 214.00 52.00 0.00 0.00 0.00 224.00 112.00 317.00 0.00 469.00 0.00 3342.0000
06-09 1857.48 147.00 616.98 121.61 278.00 0.00 221.50 117.00 0.00 0.00 0.00 333.00 97.50 388.00 0.00 455.00 0.00 4633.0650
06-10 1613.03 228.00 528.00 85.10 180.00 2.50 168.50 158.00 0.00 0.00 0.00 275.00 164.00 448.00 5.00 303.00 0.00 4158.1250
06-11 899.50 103.50 175.50 70.10 116.50 0.00 128.89 67.00 0.00 0.00 0.00 211.00 77.00 100.00 0.00 258.00 0.00 2206.9961
06-12 1168.00 167.00 288.50 87.90 133.00 2.50 162.50 59.00 0.00 0.00 0.00 194.00 113.50 207.00 28.00 222.00 0.00 2832.9000
06-13 916.00 169.50 279.00 50.00 143.50 0.00 125.50 22.00 0.00 0.00 0.00 121.00 111.00 179.00 0.00 244.50 0.00 2361.0000
06-14 887.00 118.00 268.00 56.50 143.50 2.50 137.00 29.00 0.00 0.00 0.00 144.00 29.00 223.00 0.00 300.00 0.00 2337.5000
06-15 1454.00 269.50 302.00 114.30 217.50 0.00 195.00 81.00 0.00 0.00 0.00 218.00 156.50 318.00 0.00 288.00 0.00 3613.8000
06-16 1655.50 182.50 521.00 117.40 191.00 0.00 206.50 38.00 0.00 0.00 0.00 356.00 82.00 357.00 47.00 278.00 0.00 4031.9000
06-17 1686.00 237.00 472.00 115.70 173.50 0.00 286.50 121.00 0.00 0.00 0.00 262.00 77.50 348.00 5.00 302.50 16.00 4102.7000
06-18 1079.00 182.00 297.00 104.70 115.50 0.00 231.50 45.00 0.00 0.00 0.00 139.00 68.00 272.00 15.00 232.50 8.00 2789.2000
06-19 1170.03 160.00 352.50 97.50 161.00 0.00 181.00 45.00 0.00 0.00 0.00 231.00 94.50 304.00 0.00 281.50 16.00 3094.0208
06-20 854.00 153.50 361.00 68.00 108.50 2.50 120.50 71.00 0.00 0.00 0.00 159.00 63.00 223.00 0.00 155.50 27.00 2366.5000
06-21 862.00 135.50 247.00 54.20 60.50 0.00 172.50 62.00 0.00 0.00 0.00 124.00 89.50 244.00 0.00 209.50 47.50 2308.2000
06-22 981.00 189.50 312.50 62.50 188.70 0.00 183.00 75.00 0.00 0.00 0.00 138.00 72.00 300.00 30.00 236.00 60.50 2828.7000
06-23 1251.49 280.50 517.00 86.00 242.50 0.00 247.60 103.00 0.00 0.00 0.00 123.02 135.00 537.00 6.00 319.00 48.99 3897.1032
06-24 951.00 211.50 299.50 69.00 131.00 0.00 204.00 45.00 0.00 0.00 0.00 77.00 113.50 337.00 30.00 283.50 10.50 2762.5000
06-25 1206.50 325.50 352.00 86.50 169.00 0.00 278.50 59.00 0.00 0.00 0.00 167.00 109.00 361.00 0.00 251.50 47.00 3412.5000
06-26 819.50 165.00 268.00 47.50 62.00 2.50 148.53 40.00 0.00 0.00 0.00 90.00 100.50 257.00 25.00 233.50 54.00 2313.0250
06-27 853.02 184.50 250.50 33.50 182.50 0.00 283.00 76.00 0.00 0.00 0.00 133.00 49.00 329.00 5.00 247.50 37.50 2664.0167
06-28 909.00 174.00 181.00 37.00 101.50 0.00 187.00 70.01 0.00 0.00 0.00 184.00 100.00 294.00 5.00 164.00 52.50 2459.0100
06-29 966.00 248.01 240.00 72.70 141.50 0.00 270.02 91.00 0.00 0.00 0.00 158.00 119.50 279.00 35.00 274.00 62.50 2957.2366
06-30 1225.51 242.00 401.80 68.20 115.50 5.00 221.52 24.00 0.00 0.00 0.00 192.01 159.00 457.00 15.00 254.50 67.50 3448.5300
总计 36417.56 5469.01 11284.26 2725.11 4722.40 37.50 5812.55 2192.01 0.00 0.00 0.00 5704.02 3059.00 10020.02 335.00 9000.50 555.49 97334.4306
日期 老妈火锅粉 洁美凉粉 倍佳美熟食 夏记串串 巴西烧烤 北京丁大毛冰糖葫芦 查渣面 欧意咖啡 马嫂连锅 喜来锅魁 韩式铁板烧 兰州拉面 原味小吃 黄艳 赛伦西餐厅 好味得快餐 女皇蒸凉面 日小计
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------
06-01 2309.50 247.50 1119.50 223.20 238.00 0.00 261.00 174.00 0.00 0.00 0.00 237.00 163.50 834.00 0.00 473.50 0.00 6280.7000
06-02 1643.00 218.00 663.50 130.60 172.00 10.00 211.00 83.00 0.00 0.00 0.00 174.00 156.50 658.00 28.00 484.50 0.00 4632.1000
06-03 1745.50 211.51 540.00 156.00 241.00 2.50 186.00 132.00 0.00 0.00 0.00 295.00 118.00 478.00 0.00 571.00 0.00 4676.5080
06-04 1014.50 109.00 283.00 39.30 83.00 5.00 137.00 79.00 0.00 0.00 0.00 178.00 53.00 282.00 0.00 326.50 0.00 2589.3000
06-05 962.01 116.49 208.99 60.30 143.50 0.00 106.50 33.00 0.00 0.00 0.00 138.00 99.50 187.01 0.00 266.50 0.00 2321.7842
06-06 1151.50 108.00 334.00 100.50 167.50 0.00 202.00 117.00 0.00 0.00 0.00 223.00 89.00 231.00 25.00 259.00 0.00 3007.5000
06-07 1145.50 122.00 278.50 117.30 132.20 0.00 134.50 24.00 0.00 0.00 0.00 206.00 87.00 271.01 31.00 357.00 0.00 2906.0100
06-08 1181.50 63.00 326.00 192.00 189.00 2.50 214.00 52.00 0.00 0.00 0.00 224.00 112.00 317.00 0.00 469.00 0.00 3342.0000
06-09 1857.48 147.00 616.98 121.61 278.00 0.00 221.50 117.00 0.00 0.00 0.00 333.00 97.50 388.00 0.00 455.00 0.00 4633.0650
06-10 1613.03 228.00 528.00 85.10 180.00 2.50 168.50 158.00 0.00 0.00 0.00 275.00 164.00 448.00 5.00 303.00 0.00 4158.1250
06-11 899.50 103.50 175.50 70.10 116.50 0.00 128.89 67.00 0.00 0.00 0.00 211.00 77.00 100.00 0.00 258.00 0.00 2206.9961
06-12 1168.00 167.00 288.50 87.90 133.00 2.50 162.50 59.00 0.00 0.00 0.00 194.00 113.50 207.00 28.00 222.00 0.00 2832.9000
06-13 916.00 169.50 279.00 50.00 143.50 0.00 125.50 22.00 0.00 0.00 0.00 121.00 111.00 179.00 0.00 244.50 0.00 2361.0000
06-14 887.00 118.00 268.00 56.50 143.50 2.50 137.00 29.00 0.00 0.00 0.00 144.00 29.00 223.00 0.00 300.00 0.00 2337.5000
06-15 1454.00 269.50 302.00 114.30 217.50 0.00 195.00 81.00 0.00 0.00 0.00 218.00 156.50 318.00 0.00 288.00 0.00 3613.8000
06-16 1655.50 182.50 521.00 117.40 191.00 0.00 206.50 38.00 0.00 0.00 0.00 356.00 82.00 357.00 47.00 278.00 0.00 4031.9000
06-17 1686.00 237.00 472.00 115.70 173.50 0.00 286.50 121.00 0.00 0.00 0.00 262.00 77.50 348.00 5.00 302.50 16.00 4102.7000
06-18 1079.00 182.00 297.00 104.70 115.50 0.00 231.50 45.00 0.00 0.00 0.00 139.00 68.00 272.00 15.00 232.50 8.00 2789.2000
06-19 1170.03 160.00 352.50 97.50 161.00 0.00 181.00 45.00 0.00 0.00 0.00 231.00 94.50 304.00 0.00 281.50 16.00 3094.0208
06-20 854.00 153.50 361.00 68.00 108.50 2.50 120.50 71.00 0.00 0.00 0.00 159.00 63.00 223.00 0.00 155.50 27.00 2366.5000
06-21 862.00 135.50 247.00 54.20 60.50 0.00 172.50 62.00 0.00 0.00 0.00 124.00 89.50 244.00 0.00 209.50 47.50 2308.2000
06-22 981.00 189.50 312.50 62.50 188.70 0.00 183.00 75.00 0.00 0.00 0.00 138.00 72.00 300.00 30.00 236.00 60.50 2828.7000
06-23 1251.49 280.50 517.00 86.00 242.50 0.00 247.60 103.00 0.00 0.00 0.00 123.02 135.00 537.00 6.00 319.00 48.99 3897.1032
06-24 951.00 211.50 299.50 69.00 131.00 0.00 204.00 45.00 0.00 0.00 0.00 77.00 113.50 337.00 30.00 283.50 10.50 2762.5000
06-25 1206.50 325.50 352.00 86.50 169.00 0.00 278.50 59.00 0.00 0.00 0.00 167.00 109.00 361.00 0.00 251.50 47.00 3412.5000
06-26 819.50 165.00 268.00 47.50 62.00 2.50 148.53 40.00 0.00 0.00 0.00 90.00 100.50 257.00 25.00 233.50 54.00 2313.0250
06-27 853.02 184.50 250.50 33.50 182.50 0.00 283.00 76.00 0.00 0.00 0.00 133.00 49.00 329.00 5.00 247.50 37.50 2664.0167
06-28 909.00 174.00 181.00 37.00 101.50 0.00 187.00 70.01 0.00 0.00 0.00 184.00 100.00 294.00 5.00 164.00 52.50 2459.0100
06-29 966.00 248.01 240.00 72.70 141.50 0.00 270.02 91.00 0.00 0.00 0.00 158.00 119.50 279.00 35.00 274.00 62.50 2957.2366
06-30 1225.51 242.00 401.80 68.20 115.50 5.00 221.52 24.00 0.00 0.00 0.00 192.01 159.00 457.00 15.00 254.50 67.50 3448.5300
总计 36417.56 5469.01 11284.26 2725.11 4722.40 37.50 5812.55 2192.01 0.00 0.00 0.00 5704.02 3059.00 10020.02 335.00 9000.50 555.49 97334.4306
emailqjc 2007-07-11
- 打赏
- 举报
tb1(销售记录表)
rq (日期) mnu (金额) sjbh(商家编号)
2007-06-17 2.0000 000887
2007-06-10 2.0000 000887
2007-06-12 2.0000 000887
2007-07-02 2.0000 000887
2007-06-11 2.0000 000887
2007-07-05 2.0000 000887
2007-06-10 2.0000 000887
2007-06-10 2.0000 000887
2007-07-04 2.0000 000887
2007-06-01 3.0000 100333
2007-06-01 3.0000 100340
2007-06-01 8.0000 000892
2007-06-01 4.0000 100340
2007-06-01 3.0000 100340
2007-06-01 2.5000 000888
2007-06-01 2.5000 000888
2007-06-01 10.0000 000890
2007-06-01 3.0000 100333
2007-06-01 2.5000 000888
2007-06-01 4.0000 000888
2007-06-01 4.0000 100333
2007-06-01 6.0000 100333
2007-06-01 6.0000 100333
2007-06-01 5.0000 000888
2007-06-01 3.0000 000892
2007-06-01 2.5000 000888
2007-06-01 2.0000 100333
2007-06-01 4.0000 100333
2007-06-01 5.0000 000888
2007-06-01 2.5000 000888
2007-06-01 5.0000 000888
2007-06-01 2.0000 000888
2007-06-01 4.0000 100333
2007-06-01 6.0000 100333
2007-06-01 9.0000 100333
2007-06-01 4.0000 100333
2007-06-01 3.0000 100333
2007-06-01 1.5000 000888
2007-06-01 3.0000 000888
2007-06-01 2.0000 100333
2007-06-01 4.0000 000888
2007-06-01 5.0000 000903
2007-06-01 3.0000 000892
2007-06-01 2.5000 000905
2007-06-01 3.0000 000905
2007-06-01 1.0000 000892
2007-06-01 4.0000 000888
2007-06-01 1.5000 000888
2007-06-01 3.0000 000905
2007-06-01 1.5000 000888
2007-06-01 3.0000 100333
2007-06-01 2.0000 100333
2007-06-01 3.0000 100333
2007-06-01 2.5000 100340
2007-06-01 5.0000 000888
2007-06-01 5.0000 000899
2007-06-01 2.0000 100333
2007-06-01 5.0000 000888
2007-06-01 3.0000 000892
2007-06-01 2.5000 000888
2007-06-01 2.5000 000888
tb2(商家信息)
sjbh(商家编号) sjmc(商家名称)
000393 老妈火锅粉
000887 洁美凉粉
000888 倍佳美熟食
000889 夏记串串
000890 巴西烧烤
000891 北京丁大毛冰糖葫芦
000892 查渣面
000899 欧意咖啡
000900 马嫂连锅
000901 喜来锅魁
000902 韩式铁板烧
000903 好味得快餐
000903 兰州拉面
000905 原味小吃
100333 黄艳
100339 赛伦西餐
100340 好味得快餐
100359 女皇蒸凉面
wangwang008 2007-07-11
- 打赏
- 举报
我也有一个统计问题问一下。有两个表一个是用户表userTable 一个是信息表fDataTable，其中userTable中的id对应fDataTable中的userID(其实这个不用写也可以，主要是fDataTable)。其中dueMoney是应该交的钱数，realMoney是实际交的钱数，用户可以少交，如果少交就形成了欠款写在qianMoney字段里面。问题是统计出该用户欠款的次数和金额以及正常交款的次数以及金额。急啊！
zs621 2007-07-10
- 打赏
- 举报
要是业务不固定，而且有条件时（时间）有什么更好的方法没有
paoluo 2007-07-10
- 打赏
- 举报
以上是业务类型固定只有這三種的情況下的
paoluo 2007-07-10
- 打赏
- 举报
Select
设备编号,
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 1Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
Group By
设备编号
Union All
Select
N'总',
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 1Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
设备编号,
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 1Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
Group By
设备编号
Union All
Select
N'总',
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 1Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
zs621 2007-07-10
- 打赏
- 举报
不好意思 太急了 没看清楚
paoluo 2007-07-10
- 打赏
- 举报
樓主，你測試了沒有？我寫的不是顯示日期的，就是根據日期查一定范圍的.
Declare @StartDate DateTime, @EndDate DateTime --日期范圍
Select @StartDate = '2007-07-01', @EndDate = '2007-07-10'
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = '', @S2 = ''
Select @S1 = @S1 + N', SUM(Case When Flag = 1 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [1的' + Cast(业务类型 As Varchar) + N'业务]',
@S2 = @S2 + N', SUM(Case When Flag = 2 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [2的' + Cast(业务类型 As Varchar) + N'业务]'
From (Select 日期, 业务类型 From 表1 Union Select 日期, 业务类型 From 表2) A Where 日期 Between @StartDate And @EndDate Group By 业务类型
Select @S1 = N'Select 设备编号' + @S1 + @S2 + N', SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Where 日期 Between ''' + Convert(Varchar(10), @StartDate, 120) + ''' And ''' + Convert(Varchar(10), @EndDate, 120) + ''' Group By 设备编号 Union All Select N''总''' + @S1 + @S2 + N' , SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Where 日期 Between ''' + Convert(Varchar(10), @StartDate, 120) + ''' And ''' + Convert(Varchar(10), @EndDate, 120) + ''''
EXEC(@S1)
Declare @StartDate DateTime, @EndDate DateTime --日期范圍
Select @StartDate = '2007-07-01', @EndDate = '2007-07-10'
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = '', @S2 = ''
Select @S1 = @S1 + N', SUM(Case When Flag = 1 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [1的' + Cast(业务类型 As Varchar) + N'业务]',
@S2 = @S2 + N', SUM(Case When Flag = 2 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [2的' + Cast(业务类型 As Varchar) + N'业务]'
From (Select 日期, 业务类型 From 表1 Union Select 日期, 业务类型 From 表2) A Where 日期 Between @StartDate And @EndDate Group By 业务类型
Select @S1 = N'Select 设备编号' + @S1 + @S2 + N', SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Where 日期 Between ''' + Convert(Varchar(10), @StartDate, 120) + ''' And ''' + Convert(Varchar(10), @EndDate, 120) + ''' Group By 设备编号 Union All Select N''总''' + @S1 + @S2 + N' , SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Where 日期 Between ''' + Convert(Varchar(10), @StartDate, 120) + ''' And ''' + Convert(Varchar(10), @EndDate, 120) + ''''
EXEC(@S1)
zs621 2007-07-10
- 打赏
- 举报
不好意思 没说清楚
不是显示日期 而是根据日期 查一定范围内的
paoluo 2007-07-10
- 打赏
- 举报
--如果业务类型不是固定的
Declare @StartDate DateTime, @EndDate DateTime --日期范圍
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = '', @S2 = ''
Select @S1 = @S1 + N', SUM(Case When Flag = 1 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [1的' + Cast(业务类型 As Varchar) + N'业务]',
@S2 = @S2 + N', SUM(Case When Flag = 2 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [2的' + Cast(业务类型 As Varchar) + N'业务]'
From (Select 日期, 业务类型 From 表1 Union Select 日期, 业务类型 From 表2) A Where 日期 Between @StartDate And @EndDate Group By 业务类型
Select @S1 = N'Select 设备编号' + @S1 + @S2 + N', SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Where 日期 Between ''' + Convert(Varchar(10), @StartDate, 120) + ''' And ''' + Convert(Varchar(10), @EndDate, 120) + ''' Group By 设备编号 Union All Select N''总''' + @S1 + @S2 + N' , SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Where 日期 Between ''' + Convert(Varchar(10), @StartDate, 120) + ''' And ''' + Convert(Varchar(10), @EndDate, 120) + ''''
EXEC(@S1)
Declare @StartDate DateTime, @EndDate DateTime --日期范圍
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = '', @S2 = ''
Select @S1 = @S1 + N', SUM(Case When Flag = 1 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [1的' + Cast(业务类型 As Varchar) + N'业务]',
@S2 = @S2 + N', SUM(Case When Flag = 2 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [2的' + Cast(业务类型 As Varchar) + N'业务]'
From (Select 日期, 业务类型 From 表1 Union Select 日期, 业务类型 From 表2) A Where 日期 Between @StartDate And @EndDate Group By 业务类型
Select @S1 = N'Select 设备编号' + @S1 + @S2 + N', SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Where 日期 Between ''' + Convert(Varchar(10), @StartDate, 120) + ''' And ''' + Convert(Varchar(10), @EndDate, 120) + ''' Group By 设备编号 Union All Select N''总''' + @S1 + @S2 + N' , SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Where 日期 Between ''' + Convert(Varchar(10), @StartDate, 120) + ''' And ''' + Convert(Varchar(10), @EndDate, 120) + ''''
EXEC(@S1)
zs621 2007-07-10
- 打赏
- 举报
比如
这两个表都有日期这个字段
怎么在业务不固定下加 这个条件
这两个表都有日期这个字段
怎么在业务不固定下加 这个条件
paoluo 2007-07-10
- 打赏
- 举报
你怎麼加的條件？
zs621 2007-07-10
- 打赏
- 举报
按条件查的结果不正确
zs621 2007-07-10
- 打赏
- 举报
要分不？
paoluo 2007-07-10
- 打赏
- 举报
--創建測試環境
Create Table 表1
(设备编号 Char(5),
业务类型 Int)
Insert 表1 Select '10001', 10
Union All Select '10001', 11
Union All Select '10001', 12
Union All Select '10002', 11
Union All Select '10003', 10
Union All Select '10003', 11
Create Table 表2
(设备编号 Char(5),
业务类型 Int)
Insert 表2 Select '10001' , 10
Union All Select '10001' , 10
Union All Select '10001' , 11
Union All Select '10002' , 11
Union All Select '10003' , 10
Union All Select '10003' , 11
Union All Select '10004' , 10
GO
--測試
--如果业务类型是固定的
Select
设备编号,
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [2的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [2的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [2的12业务],
SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
Group By
设备编号
Union All
Select
N'总',
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [2的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [2的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [2的12业务],
SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
--如果业务类型不是固定的
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = '', @S2 = ''
Select @S1 = @S1 + N', SUM(Case When Flag = 1 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [1的' + Cast(业务类型 As Varchar) + N'业务]',
@S2 = @S2 + N', SUM(Case When Flag = 2 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [2的' + Cast(业务类型 As Varchar) + N'业务]'
From (Select 业务类型 From 表1 Union Select 业务类型 From 表2) A Group By 业务类型
EXEC(N'Select 设备编号' + @S1 + @S2 + N', SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Group By 设备编号 Union All Select N''总''' + @S1 + @S2 + N' , SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A')
GO
--刪除測試環境
Drop Table 表1,表2
--結果
/*
设备编号 1的10业务 1的11业务 1的12业务 2的10业务 2的11业务 2的12业务 1总 2总
10001 1 1 1 2 1 0 3 3
10002 0 1 0 0 1 0 1 1
10003 1 1 0 1 1 0 2 2
10004 0 0 0 1 0 0 0 1
总 2 3 1 4 3 0 6 7
*/
Create Table 表1
(设备编号 Char(5),
业务类型 Int)
Insert 表1 Select '10001', 10
Union All Select '10001', 11
Union All Select '10001', 12
Union All Select '10002', 11
Union All Select '10003', 10
Union All Select '10003', 11
Create Table 表2
(设备编号 Char(5),
业务类型 Int)
Insert 表2 Select '10001' , 10
Union All Select '10001' , 10
Union All Select '10001' , 11
Union All Select '10002' , 11
Union All Select '10003' , 10
Union All Select '10003' , 11
Union All Select '10004' , 10
GO
--測試
--如果业务类型是固定的
Select
设备编号,
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [2的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [2的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [2的12业务],
SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
Group By
设备编号
Union All
Select
N'总',
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [2的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [2的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [2的12业务],
SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
--如果业务类型不是固定的
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = '', @S2 = ''
Select @S1 = @S1 + N', SUM(Case When Flag = 1 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [1的' + Cast(业务类型 As Varchar) + N'业务]',
@S2 = @S2 + N', SUM(Case When Flag = 2 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [2的' + Cast(业务类型 As Varchar) + N'业务]'
From (Select 业务类型 From 表1 Union Select 业务类型 From 表2) A Group By 业务类型
EXEC(N'Select 设备编号' + @S1 + @S2 + N', SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Group By 设备编号 Union All Select N''总''' + @S1 + @S2 + N' , SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A')
GO
--刪除測試環境
Drop Table 表1,表2
--結果
/*
设备编号 1的10业务 1的11业务 1的12业务 2的10业务 2的11业务 2的12业务 1总 2总
10001 1 1 1 2 1 0 3 3
10002 0 1 0 0 1 0 1 1
10003 1 1 0 1 1 0 2 2
10004 0 0 0 1 0 0 0 1
总 2 3 1 4 3 0 6 7
*/
paoluo 2007-07-10
- 打赏
- 举报
--如果业务类型是固定的
Select
设备编号,
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [2的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [2的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [2的12业务],
SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
Group By
设备编号
Union All
Select
N'总',
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [2的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [2的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [2的12业务],
SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
--如果业务类型不是固定的
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = '', @S2 = ''
Select @S1 = @S1 + N', SUM(Case When Flag = 1 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [1的' + Cast(业务类型 As Varchar) + N'业务]',
@S2 = @S2 + N', SUM(Case When Flag = 2 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [2的' + Cast(业务类型 As Varchar) + N'业务]'
From (Select 业务类型 From 表1 Union Select 业务类型 From 表2) A Group By 业务类型
EXEC(N'Select 设备编号' + @S1 + @S2 + N', SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Group By 设备编号 Union All Select N''总''' + @S1 + @S2 + N' , SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A')
GO
Select
设备编号,
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [2的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [2的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [2的12业务],
SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
Group By
设备编号
Union All
Select
N'总',
SUM(Case When Flag = 1 And 业务类型 = 10 Then 1 Else 0 End) As [1的10业务],
SUM(Case When Flag = 1 And 业务类型 = 11 Then 1 Else 0 End) As [1的11业务],
SUM(Case When Flag = 1 And 业务类型 = 12 Then 1 Else 0 End) As [1的12业务],
SUM(Case When Flag = 2 And 业务类型 = 10 Then 1 Else 0 End) As [2的10业务],
SUM(Case When Flag = 2 And 业务类型 = 11 Then 1 Else 0 End) As [2的11业务],
SUM(Case When Flag = 2 And 业务类型 = 12 Then 1 Else 0 End) As [2的12业务],
SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总],
SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总]
From
(Select *, 1 As Flag From 表1
Union All
Select *, 2 As Flag From 表2) A
--如果业务类型不是固定的
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = '', @S2 = ''
Select @S1 = @S1 + N', SUM(Case When Flag = 1 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [1的' + Cast(业务类型 As Varchar) + N'业务]',
@S2 = @S2 + N', SUM(Case When Flag = 2 And 业务类型 = ' + Cast(业务类型 As Varchar) + ' Then 1 Else 0 End) As [2的' + Cast(业务类型 As Varchar) + N'业务]'
From (Select 业务类型 From 表1 Union Select 业务类型 From 表2) A Group By 业务类型
EXEC(N'Select 设备编号' + @S1 + @S2 + N', SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A Group By 设备编号 Union All Select N''总''' + @S1 + @S2 + N' , SUM(Case When Flag = 1 Then 1 Else 0 End) As [1总], SUM(Case When Flag = 2 Then 1 Else 0 End) As [2总] From (Select *, 1 As Flag From 表1 Union All Select *, 2 As Flag From 表2) A')
GO
