求一SQL语句

[abcdef1111111]

教师保险表
CREATE TABLE A(
	[techinsid] [int] IDENTITY(1,1) NOT NULL,
	[techID] [int] NULL,
	[setdate] [nvarchar](6) COLLATE Chinese_PRC_CI_AS NULL,
	[insurances] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,----B表中的insuranceID,多个用逗号隔开
	[comppay] [money] NULL
) ON [PRIMARY]

Insert A
select N'1',N'201101',N'1,2',N'2011' union all
select ,N'1',N'201102'N'1,2,3,4',N'2011' union all
select N'1',N'201103',N'1,4',N'2011' union all
select N'1',N'201104',N'3,4',N'2011'union all
Go

保险类别表
CREATE TABLE B(
	[insuranceID] [int] IDENTITY(1,1) NOT NULL,
	[insurancename] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Insert B
select N'养老保险' union all
select ,N'医疗保险' union all
select N'失业保险' union all
select N'工伤保险'union all
GO
我想得到的效果是
techinsid  setdate  养老保险  医疗保险  失业保险  工伤保险  comppay
1          2011        1         1       0         0          2011 
2          2011        1         1       1         1          2011
3          2011        1         0       0         1          2011
4          2011        0         0       1         1          2011

1表示该月此保险已交，0表示该月此保险未交

[黄_瓜]

严重怀疑表设计 合理性

这样是节约了存储空间，但是增加了查询难度，不会写了


不是还得拆分。。。。

找点字符串拆分的例子看看

[叶涛网站推广优化]

study

[abcdef1111111]

[Quote=引用 1 楼 beirut 的回复:]
严重怀疑表设计 合理性

这样是节约了存储空间，但是增加了查询难度，不会写了


不是还得拆分。。。。

找点字符串拆分的例子看看
[/Quote]这。。。。

[liangCK]

这个很简单吧？

[abcdef1111111]

[Quote=引用 4 楼 liangck 的回复:]
这个很简单吧？
[/Quote]梁哥换头像了，梁哥给个思路，我写的语句得出的不对

[liangCK]

CREATE TABLE A(
    [techinsid] [int] IDENTITY(1,1) NOT NULL,
    [techID] [int] NULL,
    [setdate] [nvarchar](6) COLLATE Chinese_PRC_CI_AS NULL,
    [insurances] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,----B表中的insuranceID,多个用逗号隔开
    [comppay] [money] NULL
) ON [PRIMARY]

Insert A
select N'1',N'201101',N'1,2',N'2011' union all
select N'1',N'201102',N'1,2,3,4',N'2011' union all
select N'1',N'201103',N'1,4',N'2011' union all
select N'1',N'201104',N'3,4',N'2011'
Go

--保险类别表
CREATE TABLE B(
    [insuranceID] [int] IDENTITY(1,1) NOT NULL,
    [insurancename] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Insert B
select N'养老保险' union all
select N'医疗保险' union all
select N'失业保险' union all
select N'工伤保险'
GO

SELECT A.techinsid,A.techID,A.setdate,
    MAX(CASE WHEN B.insurancename='养老保险' THEN 1 ELSE 0 END) AS 养老保险,
    MAX(CASE WHEN B.insurancename='医疗保险' THEN 1 ELSE 0 END) AS 医疗保险,
    MAX(CASE WHEN B.insurancename='失业保险' THEN 1 ELSE 0 END) AS 失业保险,
    MAX(CASE WHEN B.insurancename='工伤保险' THEN 1 ELSE 0 END) AS 工伤保险
FROM A,B
WHERE CHARINDEX(','+RTRIM(B.insuranceID)+',',','+A.insurances+',')>0
GROUP BY A.techinsid,A.techID,A.setdate,A.comppay

GO
DROP TABLE A,B

[liangCK]

动态的在这呆了这么久，还不会写吗？

[abcdef1111111]

[Quote=引用 6 楼 liangck 的回复:]
SQL code
CREATE TABLE A(
[techinsid] [int] IDENTITY(1,1) NOT NULL,
[techID] [int] NULL,
[setdate] [nvarchar](6) COLLATE Chinese_PRC_CI_AS NULL,
[insurances] [nvarchar](20) COLLATE……
[/Quote]多谢梁哥，呵呵 保险类别是动态的，我再改下

[abcdef1111111]

[Quote=引用 7 楼 liangck 的回复:]
动态的在这呆了这么久，还不会写吗？
[/Quote]水平太菜，学习

[AcHerat]

create table a(
    [techinsid] [int] identity(1,1) not null,
    [techid] [int] null,
    [setdate] [nvarchar](4) collate chinese_prc_ci_as null,
    [insurances] [nvarchar](20) collate chinese_prc_ci_as null,----b表中的insuranceid,多个用逗号隔开
    [comppay] [money] null
) on [primary]

insert a
select N'1',N'2011',N'1,2',N'2011' union all
select N'1',N'2011',N'1,2,3,4',N'2011' union all
select N'1',N'2011',N'1,4',N'2011' union all
select N'1',N'2011',N'3,4',N'2011'
go

create table b(
    [insuranceid] [int] identity(1,1) not null,
    [insurancename] [nvarchar](10) collate sql_latin1_general_cp1_ci_as null
) on [primary]
insert b
select N'养老保险' union all
select N'医疗保险' union all
select N'失业保险' union all
select N'工伤保险'
go

declare @sql varchar(8000)
set @sql = 'select a.techinsid,a.setdate'
select @sql = @sql + ',sum(case b.insuranceid when ' + ltrim(insuranceid) + ' then 1 else 0 end)[' + insurancename + ']'
from b
select @sql = @sql + ',a.comppay from a left join b on charindex(ltrim(b.insuranceid),a.insurances) > 0 group by a.techinsid,a.setdate,a.comppay'
exec(@sql)

drop table a,b


/*

techinsid   setdate 养老保险        医疗保险        失业保险        工伤保险        comppay
----------- ------- ----------- ----------- ----------- ----------- ---------------------
1           2011    1           1           0           0           2011.00
2           2011    1           1           1           1           2011.00
3           2011    1           0           0           1           2011.00
4           2011    0           0           1           1           2011.00

(4 行受影响)

[我是无聊至极的人]

[Quote=引用 8 楼 abcdef1111111 的回复:]

引用 6 楼 liangck 的回复:
SQL code
CREATE TABLE A(
[techinsid] [int] IDENTITY(1,1) NOT NULL,
[techID] [int] NULL,
[setdate] [nvarchar](6) COLLATE Chinese_PRC_CI_AS NULL,
[insurances] [nvarchar](20) C……
[/Quote]

[abcdef1111111]

多谢楼上各位