Ustunlar barcha qatorlar uchun bir xil qiymatga ega bo'lishi kerak bo'lgan satrlarni tanlang

Quyidagilarni amalga oshiradigan MySQL-skriptini yozishni istayman, bu kabi ma'lumotlar bilan Buyurtma jadvali bor -

CustomerNumber | OrderNumber | Fullfilled
-----------------------------------------
001              10001         T
001              10002         T

002              10003         F
002              10004         T

003              10005         T
003              10006         T
003              10007         T

004              10008         T

Faqat bitta buyurtmaga ega bo'lgan mijozlar raqamini ko'rsatadigan so'rovlar yaratishni istayman va ularning buyurtmalari bo'yicha hamma to'liq to'ldirildi = "T".

Yuqoridagi ma'lumotlarga nisbatan so'rov CustomerNumbersning "001,003" ro'yxatiga olib keladi.

1

6 javoblar

Buni ko'ring:

SELECT CustomerNumber 
FROM mytable
GROUP BY CustomerNumber 
HAVING COUNT(DISTINCT OrderNumber) > 1 AND SUM(Fullfilled <> 'T') = 0

So'rov, HAVING moddasida ikkita taqrizni ishlatadi:

  • The first one returns customer numbers being related to more than one distinct order numbers
  • The second one filters out customer numbers being related to at least one Fullfilled = 'T' record.

    Demo here

4
qo'shib qo'ydi
SELECT CustomerNumber
FROM orders
GROUP BY Customernumber
HAVING Sum(Fullfilled = 'T') > 1

The above clause HAVING Sum(Fullfilled = 'T') > 1 will ensure that only customerNUmber that have more than 1 Fullfilled = 'T', will be brought. GROUP BY clause is used so as we can use the HAVING clause.

1
qo'shib qo'ydi

muammoni hal qilish uchun ushbu Select so'rovidan foydalanishingiz mumkin

SELECT CustomerNumber FROM `Orders` WHERE Fullfilled = 'T' GROUP BY CustomerNumber, Fullfilled HAVING COUNT(CustomerNumber)>1;

The above clause WHERE Fullfilled = 'T' GROUP BY CustomerNumber, Fullfilled ensure that only CustomerNumber has Fullfilled = 'T' and HAVING COUNT(CustomerNumber)>1 make sure that it has OrderNumber > 1

1
qo'shib qo'ydi
Ushbu sahifada w3schools.com/sql/sql_having.asp ga ega bo'lgan foydali ma'lumotlar mavjud.
qo'shib qo'ydi muallif Legatro, manba

shunga harakat qiling:

 select customerNumber from `table`
 group by customerNumber
 HAVING SUM(Fullfilled='T')>1

This code works as follows:
1- groups the costumer number with group by customerNumber
2- with HAVING and the aggregation function SUM (Fullfilled = 'T')> 1 to filter by customerNumber that are repeated more than once.

1
qo'shib qo'ydi
Yoki qilmang. Hech qanday "harakat" yo'q. A yaxshi javob har doim nima sodir qilinganligi va nima uchun u faqat OP uchun emas, balki kelajakda tashrif buyuruvchilar uchun shunday amalga oshirilganligi haqida tushuntirishlarga ega bo'ladi.
qo'shib qo'ydi muallif Jay Blanchard, manba

Siz izlayotgan narsa shu:

Select the customernumber from the orders table where fulfilled is set to T only.

Group it by the customernumber 

Having/Where the customernumber shows up more than once

So'ng SQL:

SELECT customernumber FROM orders 
WHERE fulfilled ='T'
GROUP BY customernumber 
HAVING 
COUNT(distinct customernumber) > 1);
0
qo'shib qo'ydi

Jadval yaratish/ma'lumotlarni qo'shish

CREATE TABLE t
    (`CustomerNumber` CHAR(3), `OrderNumber` INT, `Fullfilled` VARCHAR(1))
;

INSERT INTO t
    (`CustomerNumber`, `OrderNumber`, `Fullfilled`)
VALUES
    ('001', 10001, 'T'),
    ('001', 10002, 'T'),
    ('002', 10003, 'F'),
    ('002', 10004, 'T'),
    ('003', 10005, 'T'),
    ('003', 10006, 'T'),
    ('003', 10007, 'T'),
    ('004', 10008, 'T')
;

Siz xohlagan narsalarni GROUP BY, COUNT va SUM guruhlaridan o'chirib qo'yish mumkin. Ba'zi SELECTS ni natija bilan nashr etaman, shuning uchun siz istagan narsani anglashingiz mumkin.

So'rov

SELECT
     CustomerNumber
   , COUNT(DISTINCT OrderNumber) > 1 #displays 1 if CustomerNummer has more than 1 unique OrderNumbers
   , SUM(Fullfilled = 'T') #SUM up if CustomerNumber if Fullfilled = 'T' is 1 (true)
   , SUM(Fullfilled = 'T') = COUNT(DISTINCT OrderNumber) #displays 1 (true) if All records are Fullfilled = 'T' 
FROM 
 t
GROUP BY
  CustomerNumber

Natijada

CustomerNumber  COUNT(DISTINCT OrderNumber) > 1  SUM(Fullfilled = 'T')  SUM(Fullfilled = 'T') = COUNT(DISTINCT OrderNumber)  
--------------  -------------------------------  ---------------------  -----------------------------------------------------
001                                           1  2                                                                          1
002                                           1  1                                                                          0
003                                           1  3                                                                          1
004                                           0  1                                                                          1

Now to make sure to display only the records that have more the one order. We filter records out with HAVING COUNT(DISTINCT OrderNumber) > 1

So'rov

SELECT
     CustomerNumber
   , COUNT(DISTINCT OrderNumber) > 1 #displays 1 if CustomerNummer has more than 1 unique OrderNumbers
   , SUM(Fullfilled = 'T') #SUM up if CustomerNumber if Fullfilled = 'T' is 1 (true)
   , SUM(Fullfilled = 'T') = COUNT(DISTINCT OrderNumber) #display 1 if All records are Fullfilled = 'T'
FROM 
 t
GROUP BY
  CustomerNumber
HAVING
  COUNT(DISTINCT OrderNumber) > 1

Natijada

CustomerNumber  COUNT(DISTINCT OrderNumber) > 1  SUM(Fullfilled = 'T')  SUM(Fullfilled = 'T') = COUNT(DISTINCT OrderNumber)  
--------------  -------------------------------  ---------------------  -----------------------------------------------------
001                                           1  2                                                                          1
002                                           1  1                                                                          0
003                                           1  3                                                                          1

Now to make sure to display only the records that have more the one order. And we make sure we filter out records that all the records should have Fullfilled = "T". We filter records out with HAVING COUNT(DISTINCT OrderNumber) > 1 AND (SUM(Fullfilled = 'T') = COUNT(DISTINCT OrderNumber) = 1)

So'rov

SELECT
     CustomerNumber
   , COUNT(DISTINCT OrderNumber) > 1 #displays 1 if CustomerNummer has more than 1 unique OrderNumbers
   , SUM(Fullfilled = 'T') #SUM up if CustomerNumber if Fullfilled = 'T' is 1 (true)
   , SUM(Fullfilled = 'T') = COUNT(DISTINCT OrderNumber) #display 1 if All records are Fullfilled = 'T'
FROM 
 t
GROUP BY
  CustomerNumber
HAVING
    COUNT(DISTINCT OrderNumber) > 1
  AND
    (SUM(Fullfilled = 'T') = COUNT(DISTINCT OrderNumber) = 1)

Natijada

CustomerNumber  COUNT(DISTINCT OrderNumber) > 1  SUM(Fullfilled = 'T')  SUM(Fullfilled = 'T') = COUNT(DISTINCT OrderNumber)  
--------------  -------------------------------  ---------------------  -----------------------------------------------------
001                                           1  2                                                                          1
003                                           1  3                                                                          1

Oxirgi so'rov

SELECT
  CustomerNumber
FROM 
 t
GROUP BY
  CustomerNumber
HAVING
    COUNT(DISTINCT OrderNumber) > 1
  AND
    (SUM(Fullfilled = 'T') = COUNT(DISTINCT OrderNumber) = 1)

Natijada

CustomerNumber  
----------------
001             
003             
0
qo'shib qo'ydi