22,206
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS [dbo].[xxk] ;
GO
CREATE TABLE [dbo].[xxk] (
[XN] nvarchar(10) NULL ,
[XQ] NUMERIC(1) NULL ,
[KCDM] nvarchar(10) NULL ,
[KCGS] nvarchar(30) NULL ,
[SKSJ] nvarchar(255) NULL ,
[SKDD] nvarchar(255) NULL
);
GO
INSERT INTO dbo.xxk(xn,xq,kcdm,kcgs,sksj,skdd)
VALUES('2017-2018',1,'1251030','美育类','周二第5,6节{第2-3周};周二第5,6节{第5-16周}','实验大楼108西画室;实验大楼108西画室')
GO
USE tempdb
GO
IF OBJECT_ID('[dbo].[xxk]') IS NOT NULL DROP TABLE dbo.xxk;
GO
CREATE TABLE [dbo].[xxk] (
xxkId INT PRIMARY KEY, --主表主键
[XN] nvarchar(10) NULL ,
[XQ] NUMERIC(1) NULL ,
[KCDM] nvarchar(10) NULL ,
[KCGS] nvarchar(30) NULL
);
GO
--增加一个从表,主从的关系是一对多
IF OBJECT_ID('[dbo].detail') IS NOT NULL DROP TABLE dbo.detail;
CREATE TABLE detail(
id INT IDENTITY(1,1) PRIMARY KEY,
[xxkId] INT NOT NULL, --外键
[SKSJ] nvarchar(255) NULL ,
[SKDD] nvarchar(255) NULL
)
SET NOCOUNT ON
CREATE INDEX ix_detail_xxkId ON detail(xxkId)
INSERT INTO dbo.xxk(xxkId, xn,xq,kcdm,kcgs) VALUES(1,'2017-2018',1,'1251030','美育类')
INSERT INTO dbo.xxk(xxkId, xn,xq,kcdm,kcgs) VALUES(2,'2017-2018',2,'1251031','丑育类')
INSERT INTO detail(xxkId,SKSJ,SKDD) VALUES(1,'周二第5,6节{第2-3周}','实验大楼108西画室')
INSERT INTO detail(xxkId,SKSJ,SKDD) VALUES(1,'周二第5,6节{第5-16周}','实验大楼108西画室')
INSERT INTO detail(xxkId,SKSJ,SKDD) VALUES(2,'周三第5,6节{第2-3周}','实验小楼109东画室')
INSERT INTO detail(xxkId,SKSJ,SKDD) VALUES(2,'周三第5,6节{第5-16周}','实验中楼109南画室')
SELECT a.*,b.SKSJ,b.SKDD
FROM xxk AS a INNER JOIN detail AS b ON a.xxkId=b.xxkId
USE tempdb
GO
IF OBJECT_ID('[dbo].[xxk]') IS NOT NULL DROP TABLE dbo.xxk;
GO
CREATE TABLE [dbo].[xxk] (
[XN] nvarchar(10) NULL ,
[XQ] NUMERIC(1) NULL ,
[KCDM] nvarchar(10) NULL ,
[KCGS] nvarchar(30) NULL ,
[SKSJ] nvarchar(255) NULL ,
[SKDD] nvarchar(255) NULL
);
GO
SET NOCOUNT ON
INSERT INTO dbo.xxk(xn,xq,kcdm,kcgs,sksj,skdd) VALUES('2017-2018',1,'1251030','美育类','周二第5,6节{第2-3周};周二第5,6节{第5-16周}','实验大楼108西画室;实验大楼108西画室')
INSERT INTO dbo.xxk(xn,xq,kcdm,kcgs,sksj,skdd) VALUES('2017-2018',2,'1251031','丑育类','周三第5,6节{第2-3周};周三第5,6节{第5-16周}','实验小楼109东画室;实验中楼109南画室')
--
GO
--1. 增加切分字符串表值函数
IF OBJECT_ID('dbo.Fun_Split') IS NOT NULL
DROP FUNCTION [dbo].[Fun_Split]
GO
-- =============================================
-- Author : yenange
-- Create date: 2014-03-04
-- Description: 切分字符串
-- Example : SELECT * FROM [dbo].[Fun_Split]('a,b,d,c',',')
-- =============================================
CREATE FUNCTION [dbo].[Fun_Split]
(
@str NVARCHAR(MAX),
@separator NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
SELECT ROW_NUMBER() OVER (order by (select 0)) AS rowNum, B.id
FROM (
SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')
) A
OUTER APPLY(
SELECT id = N.v.value('.', 'nvarchar(max)')
FROM A.[value].nodes('/v') N(v)
) B
WHERE ISNULL(B.id,'')!=''
)
GO
--2. 你的表没有主键,无法标识每一行,增加一个自增列起主键作用
--如果你有其它列( XQ ? )是唯一的,可以不要这个pkId,将后面查询的替换一下就是了
ALTER TABLE xxk ADD pkId INT IDENTITY(1,1) NOT NULL
GO
--3. 查询
;WITH cte1 AS(
SELECT a.pkId,fs.rowNum,fs.id AS SKSJ FROM xxk AS a CROSS APPLY dbo.Fun_Split(a.SKSJ,';') AS fs
),cte2 AS(
SELECT a.pkId,fs.rowNum,fs.id AS skdd FROM xxk AS a CROSS APPLY dbo.Fun_Split(a.skdd,';') AS fs
)
SELECT a.XN
,a.XQ
,a.KCDM
,a.KCGS
--,a.pkId
,cte1.SKSJ AS SKSJ
,(SELECT TOP 1 cte2.skdd FROM cte2 WHERE cte2.pkId=cte1.pkId AND cte1.rowNum=cte2.rowNum) AS SKDD
FROM xxk AS a INNER JOIN cte1 ON a.pkId=cte1.pkId