Collect classified SQL-CodePudding

 
The create table # a 
(
ID int, 
Date a datetime, 
Project cost varchar (50), 
Cost amount numeric (10, 2), 
) 

Insert # a 
Select '1', '2020-09-20', 'travel', '200' 
The union 
Select '1', '2020-09-20', 'office', '300' 
The union 
Select '2', '2020-09-20', 'utilities',' 200 '
The union 
Select '2', '2020-09-20', 'rent', '500' 
The union 
Select '3', '2020-09-20', 'wages',' 1000 '

The create table # b 
(
ID int, 
Date a datetime, 
Payment account varchar (50), 
Payment for numeric (10, 2), 
) 

Insert # b 
Select '1', '2020-09-20', 'cash', '100' 
The union 
Select '1', '2020-09-20', 'WeChat', '400' 
The union 
Select '2', '2020-09-20', 'cash', '700' 
The union 
Select '3', '2020-09-20', 'WeChat', '500' 
The union 
Select '3', '2020-09-20', 'pay treasure', '500' 

Select * from # a 

Select * from # b 


Drop table # a 
Drop table # b

How to query into this appearance? With the join data repeated

CodePudding user response:

 


Project cost, date, SELECT a. a. a. charge amount, payment account, b. b. payment amount FROM 
(
SELECT *, ROW_NUMBER () OVER (PARTITION BY date, id ORDER BY GETDATE ()) AS x 
The FROM # a 
) a 
FULL JOIN 
(
SELECT *, ROW_NUMBER () OVER (PARTITION BY date, id ORDER BY GETDATE ()) AS y 
The FROM # b 
) ON b.I D=b AND b. Anderson, D AND x=y=a. date

CodePudding user response:

 
The create table # a 
(
ID int, 
Date a datetime, 
Project cost varchar (50), 
Cost amount numeric (10, 2), 
) 

Insert # a 
Select '1', '2020-09-20', 'travel', '200' 
The union 
Select '1', '2020-09-20', 'office', '300' 
The union 
Select '2', '2020-09-20', 'utilities',' 200 '
The union 
Select '2', '2020-09-20', 'rent', '500' 
The union 
Select '3', '2020-09-20', 'wages',' 1000 '


The create table # b 
(
ID int, 
Date a datetime, 
Payment account varchar (50), 
Payment for numeric (10, 2), 
) 

Insert # b 
Select '1', '2020-09-20', 'cash', '100' 
The union 
Select '1', '2020-09-20', 'WeChat', '300' 
The union 
Select '1', '2020-09-20', 'pay treasure', '100' 
The union 
Select '2', '2020-09-20', 'cash', '700' 
The union 
Select '3', '2020-09-20', 'WeChat', '500' 
The union 
Select '3', '2020-09-20', 'pay treasure', '500' 

- select * from # a 

- select * from # b 



Date, the SELECT, Anderson D a. a. cost project, a. charge amount, payment account, b. b. payment amount FROM 
(
SELECT *, ROW_NUMBER () OVER (PARTITION BY date, id ORDER BY GETDATE ()) AS x 
The FROM # a 
) a 
FULL JOIN 
(
SELECT *, ROW_NUMBER () OVER (PARTITION BY date, id ORDER BY GETDATE ()) AS y 
The FROM # b 
) ON b.I D=b AND b. Anderson, D AND x=y=a. date 




Drop table # a 
Drop table # b

How to just add a line of data is wrong

CodePudding user response:

 
- it is good to make a order 
Date, the SELECT, Anderson D a. a. cost project, a. charge amount, payment account, b. b. payment amount FROM 
(
SELECT *, ROW_NUMBER () OVER (PARTITION BY date, id ORDER BY GETDATE ()) AS x 
The FROM # a 
) a 
FULL JOIN 
(
SELECT *, ROW_NUMBER () OVER (PARTITION BY date, id ORDER BY GETDATE ()) AS y 
The FROM # b 
) ON b.I D=b AND b. Anderson, D AND x=y=a. date 
ORDER BY ISNULL (Anderson, d, b.i d)