统计SQL

Question

  id  qty pro  code 
  1    2  碟    002 
  2    4  书    001 
  3    3  碟    002 
  4    3  书    001 
  5    1  书/碟 001/002 
  6    1  书/碟 003/002 
  7    1  书/碟 003/001
  7    1  书/碟 002/001
大家好，请问上面的表数据中我怎样一次性统计出001.002,003的总数？

Answer 1

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-13 08:07:17
 
declare @T table([id] int,[qty] int,[pro] nvarchar(3),[code] nvarchar(7))
Insert @T
select 1,2,N'碟',N'002' union all
select 2,4,N'书',N'001' union all
select 3,3,N'碟',N'002' union all
select 4,3,N'书',N'001' union all
select 5,1,N'书/碟',N'001/002' union all
select 6,1,N'书/碟',N'003/002' union all
select 7,1,N'书/碟',N'003/001' union all
select 7,1,N'书/碟',N'002/001'
Select [001]=sum(case when charindex('/001/','/'+[code]+'/')>0 then 1 else 0 end),
       [002]=sum(case when charindex('/002/','/'+[code]+'/')>0 then 1 else 0 end),
       [003]=sum(case when charindex('/003/','/'+[code]+'/')>0 then 1 else 0 end) 
from @t
/*
001         002         003
----------- ----------- -----------
5           5           2(1 個資料列受到影響)
*/

Answer 2

declare @T table(id int,qty int,pro nvarchar(3),code nvarchar(7))
insert into @T
select 1,2,'碟','002' union all
select 2,4,'书','001' union all
select 3,3,'碟','002' union all
select 4,3,'书','001' union all
select 5,1,'书/碟','001/002' union all
select 6,1,'书/碟','003/002' union all
select 7,1,'书/碟','003/001' union all
select 7,1,'书/碟','002/001'
Select [001]=sum(case when charindex('001',code)>0 then qty else 0 end),
       [002]=sum(case when charindex('002',code)>0 then qty else 0 end),
       [003]=sum(case when charindex('003',code)>0 then qty else 0 end) 
from @t

Answer 3

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-13 08:07:17
 
declare @T table([id] int,[qty] int,[pro] nvarchar(3),[code] nvarchar(7))
Insert @T
select 1,2,N'碟',N'002' union all
select 2,4,N'书',N'001' union all
select 3,3,N'碟',N'002' union all
select 4,3,N'书',N'001' union all
select 5,1,N'书/碟',N'001/002' union all
select 6,1,N'书/碟',N'003/002' union all
select 7,1,N'书/碟',N'003/001' union all
select 7,1,N'书/碟',N'002/001'
Select [001]=sum(case when charindex('/001/','/'+[code]+'/')>0 then [qty] else 0 end),
       [002]=sum(case when charindex('/002/','/'+[code]+'/')>0 then [qty] else 0 end),
       [003]=sum(case when charindex('/003/','/'+[code]+'/')>0 then [qty] else 0 end) 
from @t
/*
001         002         003
----------- ----------- -----------
10          8           2(1 個資料列受到影響)
*/

Answer 4

如果有很多个礼品的话,是不是要一一的列举出来?可以直接用GROUP BY 吗?比如我的code还有004,005,006,007.....

Answer 5

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-13 10:19:01
 
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[qty] int,[pro] nvarchar(3),[code] nvarchar(7))
Insert tb
select 1,2,N'碟',N'002' union all
select 2,4,N'书',N'001' union all
select 3,3,N'碟',N'002' union all
select 4,3,N'书',N'001' union all
select 5,1,N'书/碟',N'001/002' union all
select 6,1,N'书/碟',N'003/002' union all
select 7,1,N'书/碟',N'003/001' union all
select 7,1,N'书/碟',N'002/001'
Go
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.[qty],[code]=substring(a.[code],b.ID,charindex('/',a.[code]+'/',b.ID)-b.ID) 
from 
    Tb a,#Num b
where
    charindex('/','/'+a.[code],b.ID)=b.ID
/*
qty         code
----------- -------
2           002
4           001
3           002
3           001
1           001
1           002
1           003
1           002
1           003
1           001
1           002
1           001(12 個資料列受到影響)
*/

Answer 6

如果要直接group by的话，5，6，7的数据就不要这样输入，数据输入不严格的话是要付出代价的

Answer 7

需要先分解,按如下方法,然后再group by 
/*
标题：分拆列值1
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-20
地点：广东深圳
描述有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/--1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #--2. 新的解决方法(sql server 2005) 
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/