Data consolidation issues, MySQL great god please come in-CodePudding

description:
A table with a total of three "chief", but not allowed to "the section chief" records in the table alone, alone if the record of "the section chief" , you need to record the merger of to of first data, (record "the section chief" username, travelname merged into the day's first data, other data will be subject to the first data,)

database table is as follows:

merge data after the renderings below :

STH over and over again for a long time, hope the mysql great god to give directions, thank you!!!!!!

CodePudding user response:

Look not to know your access logic,
Merge from ID11 and 12 to 17 and 20 merged, this logic is how come of, didn't see,
And traveladress, 2 of article 1 the value of the conditions of records?

ID19 this article, not merged again, this is what logic

CodePudding user response:

ID11 and 12 are on the same day 2020-11-17, ID12, section 2 of the data alone, need to merge to the first day, so the D12 merged into D11; ID17 ~ 20, is also the same day the 2020-11-20, ID20, section 2 data alone, need to merge into the day's first data, namely D17

CodePudding user response:

reference 1st floor morliz son hin reply:

don't understand your access logic,
Merge from ID11 and 12 to 17 and 20 merged, this logic is how come of, didn't see,
And traveladress, 2 of article 1 the value of the conditions of records?

ID19 this article, not merged again, this is what logic

reference 1st floor morliz son hin reply:

don't understand your access logic,
Merge from ID11 and 12 to 17 and 20 merged, this logic is how come of, didn't see,
And traveladress, 2 of article 1 the value of the conditions of records?

ID19 this article, not merged again, this is what logic

CodePudding user response:

reference Rebas1996 reply: 3/f

ID19 this article, not merged again, this is what logic

This logic I feel I have a problem,
If records containing 'section chief, can not exist alone, then, 17 to 20 this article 4 record (the same date range)
17 + 19 should be merged, 18 + 20 merged,,

The 18 and 20 is independent, and not merged, don't know how to understand the logic

CodePudding user response:

The last words, correct once, 18 and 19 records and exist independently, not merged, the 19 with the value of the section chief, isn't it?

CodePudding user response:

Boy, this is you want to put the original data to the merger, or through the query or SQL transformation to display the effect of the merger

CodePudding user response:

reference 5 floor morliz son hin reply:

the last words, correct once, the record of the 18th and 19th and exist independently, not merged, the 19 with the value of the section chief, isn't it?

Mean, the same day, the section chief not alone appeared in the records, can appear together with others, 19, the section chief is allowed, with others alone is not allowed in 20,
To request section chief, for example, a company can't a person on a business trip, must want to two or more, and team members can a person on a business trip, but in reality the section chief when one is on a business trip, you will need to combine the section chief the record of a person on a business trip to other travel records of the day, select merge to the day's first record here,

CodePudding user response:

refer to 6th floor three years 丿 response:

what a guy, you this is the original data to the merger, or through the query or SQL transformation to display the effect of the merger

Through the query or SQL conversion to show the effect of the merger,

CodePudding user response:

Merge requirement is for the first time, according to the date and traveladress combine username and travelname data, this use group_concat () and group by is easy to implement,
Now need on the basis of the records of "chief" alone into the that day the first data, in order to facilitate handling, I make the content of the first merger became a view,

CodePudding user response:

Test results:

 
If object_id (' tempdb for. Dbo. # travelfee ') is not null drop table # travelfee 
- build table 
The CREATE TABLE # travelfee 
(
Id int, 
The username varchar (10), 
Traveladress varchar (20), 
Travelname varchar (20), 
MDate varchar (10), 
Mark int the default (1) 
) 

- test data 
Insert into # travelfee 
Select 11, 'A1, A2, the' pearls', 'learning, learning 2', '2020-11-17', 1 union all 
Select 12, section head '1', 'here', 'test', '2020-11-17', 1 union all 
Select 13, 'A1', 'here', 'learning', '2020-11-18', 1 union all 
Section head select 14, '2', 'the guanyinqiao', 'learning 2', '2020-11-18', 1 union all 
Section head select 15, '3', 'here', 'learning', '2020-11-19', 1 union all 
Section head select 16, '1', 'pearls',' test ', '2020-11-19', 1 union all 
Select 17, 'A4, A2', 'worm', 'eat, learning', '2020-11-20', 1 union all 
18, select 'A1, A5', 'institute', 'work, singing', '2020-11-20', 1 union all 
Select 19, 'section 1, A6', 'HongYa hole', 'test, the meeting', '2020-11-20', 1 union all 
Section head select 20, '2', 'chemical', 'report', '2020-11-20', 1 

Query - 
; With the cta as (
Select *, row_number () over (partition by mDate order by getdate ()) as isFlag from # travelfee 
), 
CTB as (
Select 
Min (case when b.i sFlag=1 then b.i d else 0 end) as Id 
, the stuff ((select the ', '+ username from # travelfee where mDate=arjun Date for XML path ('')), 1, 1, ' ') as the username 
, a.t raveladress 
, a.t ravelname 
, arjun Date 

The from # travelfee as a 
Left the join the cta as b on Anderson, d=b.i d 

Group by a.t raveladress, a.t ravelname, arjun Date 
) 
Select * from CTB where Id! 
=0 order by Id

- delete 
Drop table # travelfee

The biggest problem, is on the same date, there are more than the section chief of record, how to deal with, also is the logic

CodePudding user response:

references to the tenth floor morliz son hin reply:

test results:

 
If object_id (' tempdb for. Dbo. # travelfee ') is not null drop table # travelfee 
- build table 
The CREATE TABLE # travelfee 
(
Id int, 
The username varchar (10), 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull