限制独家显示 [英] Restricting Uniques from Showing

问题描述

  SELECT b.HotelNo，g.guestName，b.dateFrom， b.dateToFrom预订b，客人g 
从预订b，客人g 
 WHERE b.guestNo = g.guestNo 
 GROUP BY b.hotelNo，b.dateFrom，b.dateTo，g。客人姓名; 
  

我得到这个输出：
输出

如何删除所有不重复的行两列？

输出将是（以字为单位）只有当他们多次住在同一家酒店时，才能获得所有客人的所有客人姓名和住宿日期）：

  1234 John Doe 2017-02-01 00：00：00.000 2017-02-28 00：00：00.000 
 1234 John Doe 2017-03-16 00：00：00.000 2017-03-21 00：00：00.000 
  

编辑：

我得到了我想要的东西，但是当我添加 b.dateFrom，b.dateTo 进入我的选择语句。我收到一条错误：列Booking.dateFrom在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

这是获取此错误的SQL查询：

  SELECT b.hotelNo ，g.guestName，b.dateFrom，b.dateTo 
 FROM Booking b，Guest g 
 WHERE b.guestNo = g.guestNo 
 GROUP BY b.hotelNo，g.guestName 
 HAVING COUNT（*）> 1; 
  

如果我添加这两个列名（我需要），我得到上面的错误。如何在没有出现错误的情况下添加他们？

解决方案

如果您只想要那些不止一次住在酒店的人使用JOIN或EXISTS子句来检查计数是否大于二。

使用 EXISTS

  SELECT DISTINCT b.hotelNo，g.guestName，b.dateFrom，b.dateTo 
 FROM Booking AS b 
 JOIN Guest AS g 
 ON b.guestNo = g.guestNo 
 WHERE EXISTS 
（
 SELECT 1 
从预订
 WHERE hotelNo = b .hotelNo 
 AND guestNo = b.guestNo 
 GROUP BY hotelNo，guestNo 
 HAVING COUNT（*）> 1 
）; 
  

使用 JOIN （但原理相同） ：

  SELECT DISTINCT b.hotelNo，g.guestName，b.dateFrom，b.dateTo 
 FROM Booking AS b 
 JOIN Guest AS g 
 ON b.guestNo = g.guestNo 
 JOIN 
（
 SELECT hotelNo，guestNo 
 FROM Booking 
 GROUP BY酒店没有，客人没有
 HAVING COUNT（*）> 1 
）AS j 
 ON j.hotelNo = b.hotelNo 
 AND j.guestNo = b.guestNo; 
  

注意：我不确定您需要 DISTINCT 或一个 GROUP BY ，除非你的表中有重复的（如果你这样做，你可能应该摆脱这些重复）。

I currently have this SQL query and this output.

SELECT b.HotelNo,g.guestName,b.dateFrom,b.dateToFrom Booking b, Guest g
FROM Booking b, Guest g
WHERE b.guestNo = g.guestNo
GROUP BY b.hotelNo,b.dateFrom,b.dateTo,g.guestName;

I am getting this output: Output

How do I remove all the rows that are not duplicates of first two columns?

Output would be (In words Get all the hotel numbers guest names and dates of stay of all guest only if they have stayed in the same hotel more than once):

1234 John Doe 2017-02-01 00:00:00.000 2017-02-28 00:00:00.000
1234 John Doe 2017-03-16 00:00:00.000 2017-03-21 00:00:00.000

EDIT:
I got what I wanted but I when I add b.dateFrom,b.dateTo into my select statement. I get an error saying: Column 'Booking.dateFrom' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is the SQL query that gets this error:

SELECT b.hotelNo,g.guestName,b.dateFrom,b.dateTo 
FROM Booking b, Guest g 
WHERE b.guestNo = g.guestNo
GROUP BY b.hotelNo,g.guestName 
HAVING COUNT(*) > 1;

If I add those two column names in (which I need) I get the error above. How do I add them in without getting an error?

解决方案

If you want only those who have stayed in a hotel more than once, use either a JOIN or an EXISTS clause to check whether the count is greater than two.

Using EXISTS:

SELECT DISTINCT b.hotelNo,g.guestName,b.dateFrom,b.dateTo 
FROM Booking AS b
JOIN Guest AS g 
    ON b.guestNo = g.guestNo
WHERE EXISTS 
(
    SELECT 1
    FROM Booking
    WHERE hotelNo = b.hotelNo
    AND guestNo = b.guestNo
    GROUP BY hotelNo, guestNo
    HAVING COUNT(*) > 1
);

Using a JOIN (but same principle):

SELECT DISTINCT b.hotelNo,g.guestName,b.dateFrom,b.dateTo 
FROM Booking AS b
JOIN Guest AS g 
    ON b.guestNo = g.guestNo
JOIN
(
    SELECT hotelNo, guestNo
    FROM Booking
    GROUP BY hotelNo, guestNo
    HAVING COUNT(*) > 1
) AS j
    ON j.hotelNo = b.hotelNo
    AND j.guestNo = b.guestNo;

NOTE: I'm not sure that you need DISTINCT or a GROUP BY on your columns unless you actually have duplicates in your tables (and if you do, you probably should get rid of those duplicates).

这篇关于限制独家显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！