For example there are three form A, B, C, the list is A single information table, the table B is album information table, the table C is the record of A person's browsing history, through the C.t ype to distinguish: 1 A, 2: B, the scan id is recorded in table C Anderson d or B.i d, how to query the information of one's entire browsing history, is to query A complete single or album information, and according to the time order

CodePudding user response:

The union should be the most intuitive,
Case the when also can satisfy the demand, table structure, and the test data, it is not posted, can oneself baidu how to write, so can deepen their understanding

CodePudding user response:

 
- test data 
- table A 
IF OBJECT_ID (' tempdb for.. # tabA ') IS NOT NULL 
DROP TABLE # tabA 
The CREATE TABLE # tabA (
Id int IDENTITY (1, 1), 
The NAME VARCHAR (20), 
) 
INSERT INTO # tabA 
SELECT the 'promise' union all 
SELECT 'mouse love rice' 
- table B 
IF OBJECT_ID (' tempdb for.. # the firm ') IS NOT NULL 
DROP TABLE # firm 
The CREATE TABLE # firm (
Id int IDENTITY (1, 1), 
The NAME VARCHAR (20), 
) 
INSERT INTO # firm 
SELECT 'jay Chou album' union all 
SELECT 'fish leong album 
- table C 
IF OBJECT_ID (' tempdb for.. # tabC ') IS NOT NULL 
DROP TABLE # tabC 
The CREATE TABLE # tabC (
Id int, 
[TYPE] VARCHAR (5), 
Dt DATETIME 
) 
INSERT INTO # tabC 
SELECT 1, '1', GETDATE () UNION ALL 
SELECT 2, '1', GETDATE () UNION ALL 
SELECT 1, '2', GETDATE () UNION ALL 
SELECT 2, '2', GETDATE () 
- the end of the test data 

; WITH cte AS (
The SELECT t1. *, t2. NAME the FROM # tabC t1 
INNER JOIN # tabA t2 ON t1. Id=t2. Id 
WHERE a t1. [TYPE]='1' 
UNION ALL 
The SELECT t1. *, t2. NAME the FROM # tabC t1 
INNER JOIN # firm t2 ON t1. Id=t2. Id 
WHERE a t1. [TYPE]='2' 
) 
SELECT * FROM cte ORDER BY dt 

Id TYPE dt NAME 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 1 2017-10-23 16:46:35. 647 promise 
2 1 2017-10-23 16:46:35. 
 647 mouse love rice1 2 2017-10-23 16:46:35. 647 jay Chou album 
2 2 2017-10-23 16:46:35. 647 fish leong album 

CodePudding user response:

 SELECT 
C.I D ID, C.T YPE Type, MAX (C.R _DATE) as' time ', A.N AME as' album ', B.N AME as' album '
The FROM 
Music A, 
ALBUM, B 
User_records C 
WHERE 
Anderson, D=C.I D AND B.I D=C.I D 
GROUP BY ID. 

CodePudding user response:

Suggest you list your table structure, and provide the test data, and based on these test data that corresponds to the correct result,
Refer to the way the post questions http://bbs.csdn.net/topics/320211382

1. You create table XXX.. Statement
2. You insert into XXX... Statement
3. The result is what kind, and give a simple algorithm description)
4. Do you use the database name and version of (often asked in MS SQL server edition MySQL)

So people who want to help you to build the same environment with you directly, and in providing the test before, avoid the error in the text understanding,