34,837
社区成员




--只针对这个
declare @sql varchar(8000)
set @sql='select jgid'
select @sql=@sql+','+csname+'=sum(case CSNAME when '''+CSNAME+''' then 1 else 0 end)'
from (select distinct CSNAME from 表名)a
set @sql=@sql+' from 表名 group by jgid'
exec(@sql)
JGID CN0001 CN0002 CN0003 CN0004 .....
J0001 2 1 2 0
J0002 0 0 0 0
SELECT JGID,CSNAME,COUNT(1) AS C FROM BCDA_VEW
GROUP BY JGID,CSNAME
/*
JGID CSNAME C
-------------------- -------------------------------------------------------------------------------- -----------
J0001 CN0001 2
J0001 CN0002 1
J0001 CN0003 2
(3 row(s) affected)
*/
USE [CSDN]
GO
IF OBJECT_ID(N'A') IS NOT NULL
BEGIN
DROP TABLE A
END
IF OBJECT_ID(N'B') IS NOT NULL
BEGIN
DROP TABLE B
END
IF OBJECT_ID(N'C') IS NOT NULL
BEGIN
DROP TABLE C
END
IF OBJECT_ID(N'D') IS NOT NULL
BEGIN
DROP TABLE D
END
GO
CREATE TABLE A (JGID VARCHAR(20),JGNAME VARCHAR(80))
CREATE TABLE B (WOMANID VARCHAR(40),WOMANNAME VARCHAR(80))
CREATE TABLE C (ID INT IDENTITY(1,1),WOMANID VARCHAR(40),CSID VARCHAR(20),OTHER VARCHAR(80))
CREATE TABLE D (CSID VARCHAR(20),CSNAME NVARCHAR(80))
GO
INSERT INTO A VALUES('J0001','JM0001')
INSERT INTO A VALUES('J0002','JM0002')
INSERT INTO A VALUES('J000001','JM000001')
INSERT INTO A VALUES('J000002','JM000002')
INSERT INTO A VALUES('J000000001','JM000000001')
INSERT INTO A VALUES('J000000002','JM000000002')
GO
INSERT INTO B VALUES('J0001W01','J0001WM01')
INSERT INTO B VALUES('J0001W02','J0001WM02')
INSERT INTO B VALUES('J0000001W01','J0000001WM01')
INSERT INTO B VALUES('J0000001W02','J0000001WM02')
INSERT INTO B VALUES('J0000000001W01','J0000000001WM01')
INSERT INTO B VALUES('J0000000001W02','J0000000001WM02')
GO
INSERT INTO D VALUES('C0001','CN0001')
INSERT INTO D VALUES('C0002','CN0002')
INSERT INTO D VALUES('C0003','CN0003')
INSERT INTO D VALUES('C0004','CN0004')
INSERT INTO D VALUES('C0005','CN0005')
INSERT INTO D VALUES('C0006','CN0006')
INSERT INTO D VALUES('C0007','CN0007')
INSERT INTO D VALUES('C0008','CN0008')
GO
INSERT INTO C VALUES('J0001W01','C0001','')
INSERT INTO C VALUES('J0001W01','C0002','')
INSERT INTO C VALUES('J0001W01','C0003','')
INSERT INTO C VALUES('J0001W02','C0001','')
INSERT INTO C VALUES('J0001W02','C0003','')
INSERT INTO C VALUES('J0000001W01','C0004','')
INSERT INTO C VALUES('J0000000001W02','C0005','')
GO
CREATE VIEW CD_VEW
AS
SELECT C.WOMANID,D.CSID,D.CSNAME FROM C C, D D
WHERE C.CSID = D.CSID
GO
CREATE VIEW BCD_VEW
AS
SELECT B.WOMANID,CD.CSID,CD.CSNAME FROM B B
LEFT JOIN CD_VEW CD ON CD.WOMANID = B.WOMANID
GO
CREATE VIEW BCDA_VEW
AS
SELECT A.JGID,BCD.WOMANID,BCD.CSID,BCD.CSNAME
FROM A A, BCD_VEW BCD
WHERE ( substring(BCD.WOMANID,1,5) = A.JGID )
OR(substring(BCD.WOMANID,1,7) = A.JGID )
OR(substring(BCD.WOMANID,1,7) = A.JGID )
GO
SELECT * FROM BCDA_VEW
/*
JGID WOMANID CSID CSNAME
-------------------- ---------------------------------------- -------------------- --------------------------------------------------------------------------------
J0001 J0001W01 C0001 CN0001
J0001 J0001W01 C0002 CN0002
J0001 J0001W01 C0003 CN0003
J0001 J0001W02 C0001 CN0001
J0001 J0001W02 C0003 CN0003
(5 row(s) affected)
*/
USE [CSDN]
GO
DROP VIEW BCDA_VEW
GO
DROP VIEW BCD_VEW
GO
DROP VIEW CD_VEW
GO
DROP TABLE A
GO
DROP TABLE B
GO
DROP TABLE C
GO
DROP TABLE D
GO