想將表中不同id使用相同item(003,004)的個數表示出來?
表A: id item
A 001
A 002
A 003
A 004
B 002
B 003
B 004
C 003
C 004
C 005
表B:id num
A 2
B 2
C 2
如何利用動態在SQL語句由表A得到表B?在線等?
表A: id item
A 001
A 002
A 003
A 004
B 002
B 003
B 004
C 003
C 004
C 005
表B:id num
A 2
B 2
C 2
如何利用動態在SQL語句由表A得到表B?在線等?
--??
select id,count(item) num from [Table] where item in('003','004') group by id
declare @a varchar(20)
set @a='003,004'
select id,count(item) num from [Table] where charindex(','+item+',',','+@a+',')>0 group by id
create table tb(id varchar(10),item varchar(10))
insert into tb values('A', '001')
insert into tb values('A', '002')
insert into tb values('A', '003')
insert into tb values('A', '004')
insert into tb values('B', '002')
insert into tb values('B', '003')
insert into tb values('B', '004')
insert into tb values('C', '003')
insert into tb values('C', '004')
insert into tb values('C', '005')
goselect id , count(*) num from tb where item in
(
select distinct item from tb group by item having count(*) = (select count(*) from (select distinct id from tb) t)
)
group by iddrop table tb/*
id num
---------- -----------
A 2
B 2
C 2(所影响的行数为 3 行)
*/
set @sql = 'select id,count(item) num from [Table] where item in(select col from a) group by id'
exec(@sql)--如果不在一个表中
declare @s1 varchar(3)
declare @s2 varchar(3)
declare @s3 varchar(3)
declare @s4 varchar(3)
....
set @1 = ''
set @2 = ''set @3 = ''
set @4 = ''
declare @sql varchar(8000)
set @sql = 'select id,count(item) num from [Table] where item in('''+@s1+''','''+@s2+''') group by id'
exec(@sql)
insert @aa values ( 'A', '002' )
insert @aa values ( 'A', '003' )
insert @aa values ( 'A', '004' )
insert @aa values ( 'B', '002' )
insert @aa values ( 'B','003' )
insert @aa values ( 'b', '004' )
insert @aa values ( 'C', '003' )
insert @aa values ( 'C', '004' )
insert @aa values ( 'C', '005' )
select * from @aaselect id,count(item) from @aa where (item='003' or item='004') group by id
=
楼主应该是查所有ID都有同一个ITEM的值我认为是这个意思.
但好象是这个意思
declare @sql varchar(8000)
set @sql = 'select id,count(item) num from [Table] where item in(select col from a) group by id'
exec(@sql)
'col'字段 從哪來的呢?