找到你要的答案

Q:how to get all possible query for a same goal?

Q:如何获得同一目标的所有可能的查询?

I wanna ask about sql. Is there possible to get all variance in sql that the goal is same?

May be use application, plugin or etc,,

Thank you

This is the example.


GOAL: “Cities with at least two suppliers”

Self-Join approach

SELECT DISTINCT City
FROM Tb_Supplier S1, Tb_Supplier S2 
WHERE S1.City=S2.City AND S1.Supp_ID<>S2.Supp_ID

Sub-Query approach

SELECT S.City
FROM Tb_Supplier S
WHERE S.City IN  
    (SELECT City
     FROM Tb_Supplier
     WHERE Supp_ID<>S.Supp_ID)

Group By approach

SELECT City 
FROM Tb_Supplier
GROUP BY City HAVING COUNT(*)>=2

我想问关于SQL。有没有可能让所有方差在SQL中,目标是一样的吗?

可以使用应用程序,插件等,,

谢谢你

这就是例子。


GOAL: “Cities with at least two suppliers”

自加入方法

SELECT DISTINCT City
FROM Tb_Supplier S1, Tb_Supplier S2 
WHERE S1.City=S2.City AND S1.Supp_ID<>S2.Supp_ID

子查询的方法

SELECT S.City
FROM Tb_Supplier S
WHERE S.City IN  
    (SELECT City
     FROM Tb_Supplier
     WHERE Supp_ID<>S.Supp_ID)

组的方法

SELECT City 
FROM Tb_Supplier
GROUP BY City HAVING COUNT(*)>=2
answer1: 回答1:

Assuming the sup_id is the PK, then you want to do the group by:

SELECT t.City
FROM 
(SELECT City, COUNT(sup_id) as [totalSuppliers]
    FROM tb_supplier
    GROUP BY City 
    HAVING COUNT(sup_id) > 1) as t

~ Cheers

假设sup_id是PK,那么你要做的组:

SELECT t.City
FROM 
(SELECT City, COUNT(sup_id) as [totalSuppliers]
    FROM tb_supplier
    GROUP BY City 
    HAVING COUNT(sup_id) > 1) as t

~干杯

sql  algorithm