代码之家 › 专栏 › 技术社区 › Freddakos

具有多个和的SQL查询语法

sql

Freddakos · 技术社区 · 7 年前

我有以下sql查询:

SELECT
    dbo.Stores.Store_Name,
    SUM(tziros_imeras) AS tziros_imeras,
    SUM(tziros_imeras_X_fpa) AS tziros_imeras_X_fpa,
    SUM(polithenta_karotsia) AS polithenta_karotsia,
    SUM(polithenta_kathismata) AS polithenta_kathismata,
    SUM(ask_for_furni) AS ask_for_furni,
    SUM(ask_for_karotsi) AS ask_for_karotsi,
    SUM(ask_for_kathisma) AS ask_for_kathisma,
    SUM(furni_reservation) AS furni_reservation,
    SUM(tziros_imeras) AS tziros_imeras
FROM
    dbo.Reports,
    dbo.Stores
WHERE
    dbo.Reports.Store_id = dbo.Stores.Store_id
GROUP BY 
    dbo.Stores.Store_Name
ORDER BY 
    dbo.Stores.Store_Name ASC

这个查询工作得很好,他做了我真正需要做的事情(我在循环中得到了期望的结果)。

我的问题是,我还需要一个信息才能包含在我的结果中。我想要列的信息 nomisma 从表格中 Nomismata .

相关表格如下2所示: Stores & 诺米斯玛塔 它们具有以下结构:

商店 (Store\u id、fpa\u id、nomisma\u id、Store\u Name、Store\u password)
诺米斯玛塔 (nomisma\u id,nomisma)

有没有什么可能的方法 诺米斯马 要在我的查询中正确包含的信息??

3 回复 | 直到 7 年前

marc_s 7 年前

首先,我建议在“FROM”子句中使用“JOIN”(内部、左侧、右侧……根据需要),从:

FROM
    dbo.Reports,
    dbo.Stores
WHERE
    dbo.Reports.Store_id = dbo.Stores.Store_id
GROUP BY 
    dbo.Stores.Store_Name
ORDER BY 
    dbo.Stores.Store_Name ASC

到

FROM
    dbo.Reports  
INNER JOIN 
    dbo.Stores ON dbo.Reports.Store_id = dbo.Stores.Store_id
GROUP BY 
    dbo.Stores.Store_Name
ORDER BY 
    dbo.Stores.Store_Name ASC

... 现在,包括您的新表:

SELECT
    dbo.Stores.Store_Name, 
    Nomismata.nomisma,
    SUM(tziros_imeras) AS tziros_imeras,
    SUM(tziros_imeras_X_fpa) AS tziros_imeras_X_fpa,
    SUM(polithenta_karotsia) AS polithenta_karotsia,
    SUM(polithenta_kathismata) AS polithenta_kathismata,
    SUM(ask_for_furni) AS ask_for_furni,
    SUM(ask_for_karotsi) AS ask_for_karotsi,
    SUM(ask_for_kathisma) AS ask_for_kathisma,
    SUM(furni_reservation) AS furni_reservation,
    SUM(tziros_imeras) AS tziros_imeras
FROM    
    dbo.Reports  
INNER JOIN 
    dbo.Stores ON dbo.Reports.Store_id = dbo.Stores.Store_id
INNER JOIN 
    Nomismata ON dbo.Stores.nomisma_id = Nomismata.nomisma_id
GROUP BY 
    dbo.Stores.Store_Name, Nomismata.nomisma
ORDER BY 
    dbo.Stores.Store_Name ASC

享受

Vash 7 年前

您可以使用 Nomismata 表格(带 Stores 表)打开 nomisma_id 柱添加 nomisma 中的列 SELECT 和 GROUP BY 条款。因为连接柱是 nomisma\u id ,我假设 诺米斯马 是一个维度,而不是要求和的数字列。

还添加了 JOIN 而不是交叉连接。这将提高查询的效率。

SELECT
dbo.Stores.Store_Name, dbo.Nomismata.nomisma,
SUM(tziros_imeras) AS tziros_imeras,
SUM(tziros_imeras_X_fpa) AS tziros_imeras_X_fpa,
SUM(polithenta_karotsia) AS polithenta_karotsia,
SUM(polithenta_kathismata) AS polithenta_kathismata,
SUM(ask_for_furni) AS ask_for_furni,
SUM(ask_for_karotsi) AS ask_for_karotsi,
SUM(ask_for_kathisma) AS ask_for_kathisma,
SUM(furni_reservation) AS furni_reservation,
SUM(tziros_imeras) AS tziros_imeras
FROM
dbo.Reports 
JOIN dbo.Stores ON dbo.Reports.Store_id = dbo.Stores.Store_id
JOIN dbo.Nomismata ON dbo.Nomismata.nomisma_id = dbo.Stores.nomisma_id
GROUP BY dbo.Stores.Store_Name, dbo.Nomismata.nomisma
ORDER BY dbo.Stores.Store_Name ASC;

ying lam 7 年前

将nomisdata连接到现有查询

选择
商店。Store\u Name,
诺米斯马塔。诺米斯马,
总和(tziros\u imeras)为tziros\u imeras,
总和(tziros\u imeras\u X\u fpa)为tziros\u imeras\u X\u fpa,
SUM(polithenta\u karotsia)作为polithenta\u karotsia,
总和(polithenta\u kathismata)为polithenta\u kathismata,
求和(ask\u for\u furni)为ask\u for\u furni,
求和(ask\u for\u karotsi)为ask\u for\u karotsi,
求和(ask\u for\u kathisma)为ask\u for\u kathisma,
SUM(furni\u reservation)作为furni\u reservation,
总和(tziros\u imeras)为tziros\u imeras
来自dbo。报告报告
内部联接dbo。门店门店
在报告上。Store\u id=门店。Store\u id
加入dbo。诺米斯马塔诺米斯马塔
在Nomismata上。nomisma\u id=存储。nomisma\u id
按门店分组。Store\u Name,
诺米斯马塔。诺米斯马
按门店订购。Store\u名称