Implementation of intersection statement

zhaozj2021-02-16  84

Run in the query analyzer:

Create Table [Test]

[ID] [INT] Identity (1, 1) Not NULL,

[Name] [NVARCHAR] (50) Collate Chinese_prc_ci_as null,

[Subject] [nvarchar] (50) collate chinese_prc_ci_as null,

[Source] [Numeric] (18, 0) NULL

) On [primary]

Go

Insert INTO [TEST] ([Name], [Subject], [Source]) VALUES (N 'Zhang San', N 'Language ", 60)

INSERT INTO [TEST] ([Name], [Subject], [SOURCE]) VALUES (N 'Li Si', N 'Mathematics ", 70)

INSERT INTO [TEST] ([Name], [Subject], [SOURCE]) VALUES (N 'King 5', N 'English ", 80)

INSERT INTO [TEST] ([Name], [Subject], [Source]) VALUES (N 'King 5', N 'Mathematics ", 75)

INSERT INTO [TEST] ([Name], [Subject], [Source]) VALUES (N 'King 5', N 'Language ", 57)

INSERT INTO [TEST] ([Name], [Subject], [Source]) VALUES (N 'Li Si', N 'Language ", 80)

INSERT INTO [TEST] ([Name], [Subject], [Source]) VALUES (N 'Zhang San', N 'English ", 100)

Go

Implementation of the intersection statement:

- Used to: The number of column tables is determined

SELECT NAME, SUM (Case Subject When 'Mathematics' Then Source Else 0 End) AS 'Mathematics',

SUM (Case Subject when 'English' Then Source Else 0 End) AS 'English',

SUM (Case Subject When 'language' Then Source Else 0 End) AS 'Language'

From test

GROUP BY Name

- Used to: The number of column tables is uncertain

Declare @SQL VARCHAR (8000)

Set @SQL = 'SELECT NAME,'

SELECT @SQL = @SQL 'SUM (Case Subject when' ' Subject ' ''

THEN SOURCE ELSE 0 END) AS '' ' SUBJECT ' ','

From (SELECT DISTINCT SUBJECT from Test) AS A

SELECT @SQL = Left (@ SQL, LEN (@SQL) -1) 'from test group by name'

EXEC (@SQL)

Go

转载请注明原文地址:https://www.9cbs.com/read-12900.html

New Post(0)