江阴网站建设,江阴网站制作,江阴网站设计,江阴SEO优化,江阴小程序开发-江阴雨辰互联

  1. JustNews 首页
  2. questions

sql - How to select all combinations of PersonID, TimetableID pairs from a time table - Stack Overflow

admin••questions•阅读5

In a workshop 3 or more persons work together with different timetables. For example, 3 PersonID's

In a workshop 3 or more persons work together with different timetables. For example, 3 PersonID's are 5215, 18943 and 21488.

I have a table tblTimetable like this:

CREATE TABLE tblTimetable
(
    PersonID INT,
    TimetableID INT
);


INSERT INTO tblTimetable (PersonID, TimetableID)
VALUES (5215, 57), (18943, 221), (18943, 230), (18943, 238),
       (21488, 257), (21488, 270), (5215, 67), (5215, 77),
       (5215, 87), (5215, 97);

With this select:

SELECT PersonID, TimetableID
FROM tblTimetable

I get this set of source rows:

PersonID    TimetableID
5215        57
18943       221
18943       230
18943       238
21488       257
21488       270
5215        67
5215        77
5215        87
5215        97

Each pair (PersonID, TimetableID) is an unchangeable solid block. I would like to select all combinations of pairs (PersonID, TimetableID).

Each TimetableID is defined in a separated table tblTimetableDetail, here it is not handled, but it will be queried later for plotting on a Microsoft Access report.

CREATE TABLE tblTimetableDetail
(
    TimetableID INT,
    WeekdayID INT,
    BeginTime DATETIME,
    EndTime DATETIME
);

So far I tried this:

SELECT PersonID, MIN(TimetableID) 
FROM tblTimetable
GROUP BY PersonID

UNION ALL

SELECT PersonID, MAX(TimetableID) 
FROM tblTimetable
GROUP BY PersonID

I got two combinations of PersonID, TimetableID pairs:

PersonID    TimetableID
5215    57
18943   221
21488   257

5215    97
18943   238
21488   270

I would like to get the exhaustive list of combinations of pairs PersonID - TimetableID, all PersonID's must be present in any combination. But the order does not matter:

CombiNo PersonID TimetableID
1       5215       57
1       18943      221
1       21488      257

2       5215       57
2       18943      221
2       21488      270

3       5215       57
3       18943      230
3       21488      257

4       5215       57
4       18943      230
4       21488      270

5       5215       57
5       18943      238
5       21488      257

6       5215       57
6       18943      238
6       21488      270



7       5215       67
7       18943      221
7       21488      257

8       5215       67
8       18943      221
8       21488      270

9       5215       67
9       18943      230
9       21488      257

10      5215       67
10      18943      230
10      21488      270

11      5215       67
11      18943      238
11      21488      257

12      5215       67
12      18943      238
12      21488      270


13       5215      77
13       18943     221
13       21488     257

14       5215      77
14       18943     221
14       21488     270

15       5215       77
15       18943    230
15       21488    257

16      5215       77
16      18943      230
16      21488      270

17      5215       77
17      18943      238
17      21488      257

18      5215       77
18      18943      238
18      21488      270


19       5215      87
19       18943     221
19       21488     257

20       5215      87
20       18943     221
20       21488     270

21       5215       87
21       18943    230
21       21488    257

22      5215       87
22      18943      230
22      21488      270

23      5215       87
23      18943      238
23      21488      257

24      5215       87
24      18943      238
24      21488      270


25       5215      97
25       18943     221
25       21488     257

26       5215      97
26       18943     221
26       21488     270

27       5215       97
27       18943    230
27       21488    257

28      5215       97
28      18943      230
28      21488      270

29      5215       97
29      18943      238
29      21488      257

30      5215       97
30      18943      238
30      21488      270

How to achieve this with T-SQL?

With a good SQL string, I can make a PassThrough query as .RecordSource for a report, drawing 30 pages of timetable, each combination per page.

In a workshop 3 or more persons work together with different timetables. For example, 3 PersonID's are 5215, 18943 and 21488.

I have a table tblTimetable like this:

CREATE TABLE tblTimetable
(
    PersonID INT,
    TimetableID INT
);


INSERT INTO tblTimetable (PersonID, TimetableID)
VALUES (5215, 57), (18943, 221), (18943, 230), (18943, 238),
       (21488, 257), (21488, 270), (5215, 67), (5215, 77),
       (5215, 87), (5215, 97);

With this select:

SELECT PersonID, TimetableID
FROM tblTimetable

I get this set of source rows:

PersonID    TimetableID
5215        57
18943       221
18943       230
18943       238
21488       257
21488       270
5215        67
5215        77
5215        87
5215        97

Each pair (PersonID, TimetableID) is an unchangeable solid block. I would like to select all combinations of pairs (PersonID, TimetableID).

Each TimetableID is defined in a separated table tblTimetableDetail, here it is not handled, but it will be queried later for plotting on a Microsoft Access report.

CREATE TABLE tblTimetableDetail
(
    TimetableID INT,
    WeekdayID INT,
    BeginTime DATETIME,
    EndTime DATETIME
);

So far I tried this:

SELECT PersonID, MIN(TimetableID) 
FROM tblTimetable
GROUP BY PersonID

UNION ALL

SELECT PersonID, MAX(TimetableID) 
FROM tblTimetable
GROUP BY PersonID

I got two combinations of PersonID, TimetableID pairs:

PersonID    TimetableID
5215    57
18943   221
21488   257

5215    97
18943   238
21488   270

I would like to get the exhaustive list of combinations of pairs PersonID - TimetableID, all PersonID's must be present in any combination. But the order does not matter:

CombiNo PersonID TimetableID
1       5215       57
1       18943      221
1       21488      257

2       5215       57
2       18943      221
2       21488      270

3       5215       57
3       18943      230
3       21488      257

4       5215       57
4       18943      230
4       21488      270

5       5215       57
5       18943      238
5       21488      257

6       5215       57
6       18943      238
6       21488      270



7       5215       67
7       18943      221
7       21488      257

8       5215       67
8       18943      221
8       21488      270

9       5215       67
9       18943      230
9       21488      257

10      5215       67
10      18943      230
10      21488      270

11      5215       67
11      18943      238
11      21488      257

12      5215       67
12      18943      238
12      21488      270


13       5215      77
13       18943     221
13       21488     257

14       5215      77
14       18943     221
14       21488     270

15       5215       77
15       18943    230
15       21488    257

16      5215       77
16      18943      230
16      21488      270

17      5215       77
17      18943      238
17      21488      257

18      5215       77
18      18943      238
18      21488      270


19       5215      87
19       18943     221
19       21488     257

20       5215      87
20       18943     221
20       21488     270

21       5215       87
21       18943    230
21       21488    257

22      5215       87
22      18943      230
22      21488      270

23      5215       87
23      18943      238
23      21488      257

24      5215       87
24      18943      238
24      21488      270


25       5215      97
25       18943     221
25       21488     257

26       5215      97
26       18943     221
26       21488     270

27       5215       97
27       18943    230
27       21488    257

28      5215       97
28      18943      230
28      21488      270

29      5215       97
29      18943      238
29      21488      257

30      5215       97
30      18943      238
30      21488      270

How to achieve this with T-SQL?

With a good SQL string, I can make a PassThrough query as .RecordSource for a report, drawing 30 pages of timetable, each combination per page.

  • sql
  • sql-server
  • t-sql
Share Improve this question edited Mar 10 at 7:23 jacouh asked Mar 7 at 9:11 jacouhjacouh 8,7875 gold badges35 silver badges44 bronze badges 9
  • 1 Doesn't "set" thing depends on what kind of combinations you have? Probably need a better example – siggemannen Commented Mar 7 at 9:36
  • @siggemannen, x is person's unique number, y are references of time schedules for each week, 1 person has multiple weekly time schedules depending on season. so each set I've all x values with 1 single y - corresponding to time table. – jacouh Commented Mar 7 at 9:47
  • What result would you want if you had another x value with only two y values, which one would you want to double up to make three rows? – Charlieface Commented Mar 7 at 10:17
  • 2 Voting on closing, it's not quite clear what the logic is, we need a couple of more rows that aren't just 1 <=>1 combo and what it sohuld return – siggemannen Commented Mar 7 at 11:53
  • 1 If y is supposed to represent working hours, why not use numbers that actually look like working hours? Why not call the columns PersonID and WorkingHours instead of x and y? Your question might make more sense instead of making it completely opaque to save a few characters in code. That's not the kind of brevity that's useful to anyone. And as the others have said, you need more examples. What if another x had 3 unique values? What if an x had 4 unique values? What if an x/y combo also had 4 unique values? We can solve the simple case but it probably won't be good enough. – Aaron Bertrand Commented Mar 7 at 12:12
 |  Show 4 more comments

5 Answers 5

Reset to default 1

fiddle

Provided that you have access to GENERATE_SERIES() from SQL Server 2022 onwards, you don't need recursion or joins, just a little bit of maths and duplicating rows with appropriate IDs.

In your case, there are 30 combinations (5 from person A * 3 from person B * 2 from person c == 30)

This means that:

  • each row from person A needs duplicating 6 times

  • each row from person B needs duplicating 10 times

  • each row from person C needs duplicating 15 times

Each duplicate needs a different combo_id, for example, for person A:

Original Row# 1st combo# 2nd combo# 3rd combo# 4th combo# 5th combo# 6th combo#
1 1 6 11 16 21 26
2 2 7 12 17 22 27
3 3 8 13 18 23 28
4 4 9 14 19 24 29
5 5 10 15 20 25 30

This can be achieved with GENERATE_SERIES(original_row_id, 30, 5) where 30 is the total number of combinations, and 5 is the number of rows the current person has.

Using CROSS APPLY means that we can generate a different series (generating different number of duplicates, with differing combination_d values) for each person and each of their rows.

CREATE TABLE timetable
(
  person_id      INT,
  timetable_id   INT
);


INSERT INTO
  timetable
VALUES
  (5215,   57), (5215,   67), (5215,   77), (5215,   87), (5215,   97),
  (18943, 221), (18943, 230), (18943, 238),
  (21488, 257), (21488, 270)
;

WITH
  summary (person_id, timetable_id, person_rows, person_row_ix)
AS
(
  SELECT
    person_id,
    timetable_id,
    COUNT(*) OVER (PARTITION BY person_id),
    CAST(ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY timetable_id) AS INT)
  FROM
    timetable
),
  mapping
AS
(
  SELECT
    *,
    -- PRODUCT AGGREGATE, in this case, 5*1*1*1*1 * 3*1*1 * 2*1 == 30
    CAST(
      ROUND(
        EXP(
          SUM(CASE WHEN person_row_ix = 1 THEN LOG(person_rows) ELSE 0 END)
          OVER ()
        ),
        0
      )
      AS INT
    )
      AS total_combinations
  FROM
    summary
)
SELECT
  value AS combination_id,
  mapping.*
FROM
  mapping
CROSS APPLY
  GENERATE_SERIES(
    person_row_ix,
    total_combinations,
    person_rows
  )
ORDER BY
  value,
  person_id
combination_id person_id timetable_id person_rows person_row_ix total_combinations
1 5215 57 5 1 30
1 18943 221 3 1 30
1 21488 257 2 1 30
2 5215 67 5 2 30
2 18943 230 3 2 30
2 21488 270 2 2 30
3 5215 77 5 3 30
3 18943 238 3 3 30
3 21488 257 2 1 30
4 5215 87 5 4 30
4 18943 221 3 1 30
4 21488 270 2 2 30
5 5215 97 5 5 30
5 18943 230 3 2 30
5 21488 257 2 1 30
6 5215 57 5 1 30
6 18943 238 3 3 30
6 21488 270 2 2 30
7 5215 67 5 2 30
7 18943 221 3 1 30
7 21488 257 2 1 30
8 5215 77 5 3 30
8 18943 230 3 2 30
8 21488 270 2 2 30
9 5215 87 5 4 30
9 18943 238 3 3 30
9 21488 257 2 1 30
10 5215 97 5 5 30
10 18943 221 3 1 30
10 21488 270 2 2 30
11 5215 57 5 1 30
11 18943 230 3 2 30
11 21488 257 2 1 30
12 5215 67 5 2 30
12 18943 238 3 3 30
12 21488 270 2 2 30
13 5215 77 5 3 30
13 18943 221 3 1 30
13 21488 257 2 1 30
14 5215 87 5 4 30
14 18943 230 3 2 30
14 21488 270 2 2 30
15 5215 97 5 5 30
15 18943 238 3 3 30
15 21488 257 2 1 30
16 5215 57 5 1 30
16 18943 221 3 1 30
16 21488 270 2 2 30
17 5215 67 5 2 30
17 18943 230 3 2 30
17 21488 257 2 1 30
18 5215 77 5 3 30
18 18943 238 3 3 30
18 21488 270 2 2 30
19 5215 87 5 4 30
19 18943 221 3 1 30
19 21488 257 2 1 30
20 5215 97 5 5 30
20 18943 230 3 2 30
20 21488 270 2 2 30
21 5215 57 5 1 30
21 18943 238 3 3 30
21 21488 257 2 1 30
22 5215 67 5 2 30
22 18943 221 3 1 30
22 21488 270 2 2 30
23 5215 77 5 3 30
23 18943 230 3 2 30
23 21488 257 2 1 30
24 5215 87 5 4 30
24 18943 238 3 3 30
24 21488 270 2 2 30
25 5215 97 5 5 30
25 18943 221 3 1 30
25 21488 257 2 1 30
26 5215 57 5 1 30
26 18943 230 3 2 30
26 21488 270 2 2 30
27 5215 67 5 2 30
27 18943 238 3 3 30
27 21488 257 2 1 30
28 5215 77 5 3 30
28 18943 221 3 1 30
28 21488 270 2 2 30
29 5215 87 5 4 30
29 18943 230 3 2 30
29 21488 257 2 1 30
30 5215 97 5 5 30
30 18943 238 3 3 30
30 21488 270 2 2 30

fiddle

with tbltmp(x, y) as (
    select * from (
        VALUES 
            (5215, 57), 
            (18943, 221), 
            (18943, 230), 
            (18943, 238), 
            (21488, 257), 
            (21488, 270), 
            (5215, 67), 
            (5215, 77), 
            (5215, 87), 
            (5215, 97)  
    ) as t(x, y)
),
rtbltmp as (
    select d.*, 
        dense_rank() over(order by x) as rnx,
        row_number() over(partition by x order by y) as rny,
        count(y) over(partition by x) as  cnt 
    from tbltmp d
)
, solution(x,y, rnx, rny, js) as (
    select x,y, rnx, rny, cast(concat('[' , x , ',' , y , ']') as varchar(max))
    from rtbltmp
    where rnx = 1 
    
    union all
    
    select d.x, d.y, d.rnx, d.rny, 
        concat(r.js, ',[',  d.x,  ',', d.y, ']')
    from solution r
    join rtbltmp d
        on 
            (d.rnx = r.rnx + 1)
),
jsolution(grp, js) as (
    select row_number() over(order by rnx), concat('[', js, ']') as js from solution
    where rnx = (select count(distinct x) from rtbltmp)
)
select * from jsolution ;

https://dbfiddle.uk/PLiK04hT

Left to you to convert the JSON arrays in rows using SQLServer syntax.

(ORACLE version as example: https://dbfiddle.uk/6VGG4Uad)

We can consider this task as making unique combinations of the members of a sets - each combination should have 1 member of each set.

With this test data we have 3 sets

set 5215(57, 67, 77, 87 97) - 5 members 
set 18943(221, 230, 238) - 3 members
set 21488(257, 270) - 2 members

So combination count is 5*3*2=30 - totOver.
We can multiply (generate series) each row to (totOver/rQty) rows.
For example

(5215,57) to (5215,57,1),(5215,57,2)...(5215,57,6)
(5215,67) to (5215,67,7),(5215,67,8)...(5215,67,12)
...
(5215,97) to (5215,97,25),(5215,97,26)...(5215,97,30)

Last number in triple is group num.

See example

PersonId xCnt grn
5215 5 3
18943 3 2
21488 2 1

PrepRn output

PersonId TimetableID rQty rnx fromN toN totOver
5215 57 5 1 1 6 30
5215 67 5 2 7 12 30
5215 77 5 3 13 18 30
5215 87 5 4 19 24 30
5215 97 5 5 25 30 30
18943 221 3 1 1 10 30
18943 230 3 2 11 20 30
18943 238 3 3 21 30 30
21488 257 2 1 1 15 30
21488 270 2 2 16 30 30 
with  PersonCounts as(
   select PersonId,count(*)xCnt,row_number()over(order by count(*))grn
   from tblTimetable
   group by PersonId
)
,totCounts as(  -- recursion  to count 5*3*2
   select PersonId,xCnt,grn, xCnt totCnt 
   from PersonCounts    where grn=1
   union all
   select t.PersonId,t.xCnt,t.grn,r.totCnt*t.xCnt 
   from totCounts r 
   inner join PersonCounts t on t.grn=(r.grn+1)
)
,rcTot as( -- take max value 5*3*2=30
  select *,max(totCnt)over()totOver from totCounts
)
,PrepRn as(  -- calculate indexes for every row fromN - toN
  select PersonId,TimetableID,rQty,rnx
    ,(rnx-1)*totOver/rqty+1 fromN
    ,rnx*totOver/rqty toN
    ,totOver
  from(
    select t.PersonID,TimetableID
      ,row_number()over(partition by t.PersonId order by t.TimetableID) rnx
      ,count(*)over(partition by t.PersonID ) rQty
      ,totOver
    from tblTimetable t
    inner join rcTot t2 on t2.PersonID=t.PersonID
  )a
)
select t.PersonId,TimetableID,fromN rnx,toN ,nn.value grn
    ,rQty,totOver
from PrepRn t
cross apply generate_series(fromN,toN)nn
order by grn,PersonId;
PersonId TimetableID rnx toN grn rQty totOver
5215 57 1 6 1 5 30
18943 221 1 10 1 3 30
21488 257 1 15 1 2 30
5215 57 1 6 2 5 30
18943 221 1 10 2 3 30
21488 257 1 15 2 2 30
5215 57 1 6 3 5 30
18943 221 1 10 3 3 30
21488 257 1 15 3 2 30

.....

fiddle

Try removing the hits for x from the original table, like in this cte query. You may prepare a stored procedure based on this logic or add recursiveness for the cte query if possible (I'm sorry I'm not good in that.)

with cte_a
as
(
    select
        x,
        min(y) as y1,
        count(*) as cnt
    from tblTmp
    group by x
),

cte_b
as
(
    select
        x,
        min(y) as y2,
        count(*) as cnt
    from tblTmp where y <> (select y1 from cte_a where cnt > 1)
    group by x
),

cte_c
as
(
    select
        x,
        min(y) as y,
        count(*) as cnt
    from tblTmp where y not in (select y1 from cte_a where cnt > 1 union select y2 from cte_b where cnt > 1)
    group by x
)



select * from cte_a union all select * from cte_b union all select * from cte_c

select *
from tblTimetable t1 cross join tblTimetable t2 cross join tblTimetable t3
where t1.PersonId < t2.PersonId and t2.PersonId < t3.PersonId;

Do 3-way cross join. A given person will not be picked more than once because of the inequalities.

发布者：admin，转转请注明出处：http://www.yc00.com/questions/1744940005a4602263.html

adminadmin
0

相关推荐

发表回复

评论列表（0条）

  • 暂无评论

联系我们

400-800-8888

在线咨询： QQ交谈

邮件：admin@example.com

工作时间：周一至周五，9:30-18:30，节假日休息

关注微信