查询多个表 [英] Querying multiple tables

问题描述

I have two tables
Table 1 looks like this

|ID	| Repeats|
-----------
|A|	1
|A| 1
|A|	0
|B|	2
|B|	2
|C|	2
|D|	1

Table 2 looks like this
|ID	|values|
-----------
|A	|100|
|B	|200|
|C	|100|
|D	|300|

Using a view I need a result like this
|ID	|values	|Repeats|
-------------------
|A	|100|	NA|
|B	|200|	2|
|C	|100|	2|
|D	|300|	1[enter image description here][1]

that means, I want unique ID, its values and Repeats. Repeats value should display NA when there are multiple values against single ID and it should display the Repeats value in case there is single value for repeats.

Initially I needed to display the max value of repeats so I tried the following view

ALTER VIEW [dbo].[BookingView1]
AS
SELECT bv.*, bd2.Repeats FROM Table1 bv 
JOIN
(
    SELECT distinct bd.id, bd.Repeats FROM table2 bd
    JOIN
    (
      SELECT Id, MAX(Repeats) AS MaxRepeatCount
      FROM table2
      GROUP BY Id
    ) bd1
    ON bd.Id = bd1.Id
    AND bd.Repeats = bd1.MaxRepeatCount
) bd2
ON bv.Id = bd2.Id;

and this returns the correct result but when trying to implement the CASE it fails to return unique ID results. Please help!!

The following link shows the tables.

  [1]: http://i.stack.imgur.com/xN8CP.jpg

我尝试过：



ALTER VIEW [dbo]。[BookingView1]

AS

SELECT bv。* ，bd2.Repeats FROM Table1 bv

JOIN

（

SELECT distinct bd.id，bd.Repeats FROM table2 bd

JOIN

（

SELECT Id，MAX（重复）AS MaxRepeatCount

FROM table2

GROUP BY Id

）bd1

ON bd.Id = bd1.Id

AND bd.Repeats = bd1.MaxRepeatCount

）bd2

ON bv.Id = bd2.Id;



这会返回正确的结果，但在尝试实施CASE时无法返回唯一ID结果。请帮助!!

What I have tried:

ALTER VIEW [dbo].[BookingView1]
AS
SELECT bv.*, bd2.Repeats FROM Table1 bv
JOIN
(
SELECT distinct bd.id, bd.Repeats FROM table2 bd
JOIN
(
SELECT Id, MAX(Repeats) AS MaxRepeatCount
FROM table2
GROUP BY Id
) bd1
ON bd.Id = bd1.Id
AND bd.Repeats = bd1.MaxRepeatCount
) bd2
ON bv.Id = bd2.Id;

and this returns the correct result but when trying to implement the CASE it fails to return unique ID results. Please help!!

推荐答案

根据示例数据，这样的事情应该有效：

Based on the sample data, something like this should work:

WITH cteRepeats As
(
    SELECT
        ID,
        Min(Repeats) As MinRepeats,
        Max(Repeats) As MaxRepeats
    FROM
        Table1
    GROUP BY
        ID
)
SELECT
    T2.ID,
    T2.Values,
    CASE
        WHEN T1.MinRepeats = T1.MaxRepeats THEN CAST(T1.MinRepeats As varchar(10))
        ELSE 'NA'
    END As Repeats
FROM
    Table2 As T2
    LEFT JOIN cteRepeats As T1
    ON T1.ID = T2.ID
;

这篇关于查询多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！