Início / dba / Perguntas / 29415
Accepted
Jack
Asked: 2012-11-28 23:00:15 +0800 CST

Mesclar e formatar dados de duas tabelas

  • 772

Eu tenho uma tabela chamada Slotda seguinte forma com dados padrão:

1ª Mesa

================================
|  Day  | Time  | Venue | Free |
================================
|   1   | 0830  | RM 1  |  10  |
|   1   | 0830  | RM 2  |  10  |
|   1   | 1030  | RM 1  |  20  |
|   1   | 1030  | RM 2  |  20  |
|   2   | 0830  | RM 1  |  10  |
|   2   | 0830  | RM 2  |  10  |
|   2   | 1030  | RM 1  |  30  |
|   2   | 1030  | RM 2  |  30  |
================================

Há outra tabela Bookingcom dados que podem ir e vir a qualquer momento, mas o cabeçalho da coluna é fixo:

2ª Mesa

===================================
|  Day  | Time  | Venue |  User   |
===================================
|   1   | 0830  | RM 1  |  Jill   |
|   1   | 0830  | RM 2  |  Jill   |
|   1   | 0830  | RM 1  |  Jack   |
|   1   | 0830  | RM 1  |  Mary   |
|   1   | 0830  | RM 2  |  Mary   |
|   1   | 0830  | RM 2  |  Jill   |
|   2   | 1030  | RM 1  |  Ken    |
|   2   | 1030  | RM 1  |  Ken    |
====================================

Com base nos dados de exemplo na tabela Booking, como posso derivar a tabela a seguir?

3ª Mesa (Era isso que eu queria) 

=======================================
|  Day  | Time  | Venue | Free | Used |
=======================================
|   1   | 0830  | RM 1  |  10  |  3   |
|   1   | 0830  | RM 2  |  10  |  3   |
|   1   | 1030  | RM 1  |  20  |  0   |
|   1   | 1030  | RM 2  |  20  |  0   |
|   2   | 0830  | RM 1  |  10  |  0   |
|   2   | 0830  | RM 2  |  10  |  0   |
|   2   | 1030  | RM 1  |  30  |  2   |
|   2   | 1030  | RM 2  |  30  |  0   |
=======================================

Eu sou capaz de recuperar a seguinte tabela

4ª Mesa

================================
|  Day  | Time  | Venue | Used |
================================
|   1   | 0830  | RM 1  |  3   |
|   1   | 0830  | RM 2  |  3   |
|   2   | 1030  | RM 1  |  2   |
================================

usando o seguinte comando

select 
    day, time, venue, COUNT(*) as Used
from 
    booking 
group by 
    day, time, venue 
order by 
    day asc, time asc, time asc

mas acho difícil mesclar e obter a 3ª mesa que eu queria.

sql-server-2008

2 respostas

  1. Best Answer

    Criação de tabela e script de dados de amostra:

    CREATE TABLE #Slot
(
    [Day]   tinyint NOT NULL,
    [Time]  time(0) NOT NULL,
    Venue   char(4) NOT NULL,
    Free    smallint NOT NULL CHECK (Free > 0),

    PRIMARY KEY ([Day], [Time], Venue)
);

INSERT #Slot
    ([Day], [Time], Venue, Free)
VALUES
    (1, '08:30', 'RM 1', 10),
    (1, '08:30', 'RM 2', 10),
    (1, '10:30', 'RM 1', 20),
    (1, '10:30', 'RM 2', 20),
    (2, '08:30', 'RM 1', 10),
    (2, '08:30', 'RM 2', 10),
    (2, '10:30', 'RM 1', 30),
    (2, '10:30', 'RM 2', 10);

CREATE TABLE #Booking
(
    [Day]   tinyint NOT NULL,
    [Time]  time(0) NOT NULL,
    Venue   char(4) NOT NULL,
    [User]  varchar(10) NOT NULL
);

INSERT #Booking
    ([Day], [Time], Venue, [User])
VALUES
    (1, '08:30', 'RM 1', 'Jill'),
    (1, '08:30', 'RM 2', 'Jill'),
    (1, '08:30', 'RM 1', 'Jack'),
    (1, '08:30', 'RM 1', 'Mary'),
    (1, '08:30', 'RM 2', 'Mary'),
    (1, '08:30', 'RM 2', 'Jill'),
    (2, '10:30', 'RM 1', 'Ken'),
    (2, '10:30', 'RM 1', 'Ken');

    Consulta:

    SELECT
    s.[Day],
    s.[Time],
    s.Venue,
    s.Free,
    Used =
    (
        SELECT COUNT_BIG(*)
        FROM #Booking AS b
        WHERE
            b.[Day] = s.[Day]
            AND b.[Time] = s.[Time]
            AND b.Venue = s.Venue
    )
FROM #Slot AS s
ORDER BY
    s.[Day],
    s.[Time],
    s.Venue;

    Resultado:

    ╔═════╦══════════╦═══════╦══════╦══════╗
║ Day ║   Time   ║ Venue ║ Free ║ Used ║
╠═════╬══════════╬═══════╬══════╬══════╣
║   1 ║ 08:30:00 ║ RM 1  ║   10 ║    3 ║
║   1 ║ 08:30:00 ║ RM 2  ║   10 ║    3 ║
║   1 ║ 10:30:00 ║ RM 1  ║   20 ║    0 ║
║   1 ║ 10:30:00 ║ RM 2  ║   20 ║    0 ║
║   2 ║ 08:30:00 ║ RM 1  ║   10 ║    0 ║
║   2 ║ 08:30:00 ║ RM 2  ║   10 ║    0 ║
║   2 ║ 10:30:00 ║ RM 1  ║   30 ║    2 ║
║   2 ║ 10:30:00 ║ RM 2  ║   10 ║    0 ║
╚═════╩══════════╩═══════╩══════╩══════╝
    • 6 
  2. CREATE TABLE #slot (
    Day TINYINT,
    Time CHAR(4),
    Venue VARCHAR(10),
    Free INT
)
INSERT INTO #slot (Day, Time, Venue, Free) VALUES
(1, '0830', 'RM 1', 10),
(1, '0830', 'RM 2', 10),
(1, '1030', 'RM 1', 20),
(1, '1030', 'RM 2', 20),
(2, '0830', 'RM 1', 10),
(2, '0830', 'RM 2', 10),
(2, '1030', 'RM 1', 30),
(2, '1030', 'RM 2', 30)


CREATE TABLE #booking (
    Day TINYINT,
    Time CHAR(4),
    Venue VARCHAR(10),
    [User] VARCHAR(30)
)

INSERT INTO #booking (Day, Time, Venue, [User]) VALUES
(1, '0830', 'RM 1', 'Jill'),
(1, '0830', 'RM 2', 'Jill'),
(1, '0830', 'RM 1', 'Jack'),
(1, '0830', 'RM 1', 'Mary'),
(1, '0830', 'RM 2', 'Mary'),
(1, '0830', 'RM 2 ', 'Jill'),
(2, '1030', 'RM 1', 'Ken'),
(2, '1030', 'RM 1', 'Ken')

SELECT
    s.Day,
    s.Time,
    s.Venue,
    s.Free,
    ISNULL(g.Used, 0) AS Used
FROM #slot s

    LEFT JOIN (
        SELECT
            Day,
            Time,
            Venue,
            COUNT(*) AS Used
        FROM #booking 
        GROUP BY
            Day,
            Time,
            Venue
    ) g
        ON g.Day = s.day
        AND g.Time = s.Time
        AND g.Venue = s.Venue

DROP TABLE #slot
DROP TABLE #booking
    • 4

relate perguntas