时间戳

首页 > 解决方案 > SQL中的数字相除

sql - SQL中的数字相除

问题描述

我有一个包含产品订单的数据库，我需要将男性订单与每年的所有订单分开。

我成功打印了每年的所有订单和每年的男士订单，但我不知道如何显示除以数字的数字。

SELECT 
    YEAR(o.Order_Date) AS year,
    COUNT(o.order_id) AS number_of_orders
FROM
    [targil 2].[dbo].[exc2 - product] p, [targil 2].[dbo].[exc2 - orders] o
WHERE 
    p.Product_ID = o.Product_ID 
GROUP BY
    YEAR(o.Order_Date);

输出 =

   year    number_of_orders
   -------------------------
   2012        540
   2013        512
   2014        180

SELECT DISTCINT
    YEAR(o.Order_Date) AS year, 
    COUNT(o.order_id) AS number_of_orders
FROM
    [targil 2].[dbo].[exc2 - product] p, [targil 2].[dbo].[exc2 - orders] o
WHERE 
    p.Product_ID = o.Product_ID 
    AND p.Product_Name LIKE '%men%'
GROUP BY
    YEAR(o.Order_Date);

输出

year  number_of_orders
----------------------
2012       50
2013      146
2014      138

期望的输出

year   number_of_men_orders_of_all_orders
----------------------------------------
2012        0.0925
2013        0.285
2014        0.766

标签： sql

解决方案

您使用的是什么风格的 SQL？它看起来像 MS Sql Server。

您必须在他们的年份加入您的两个结果集，以便比较每个结果集的数量。尝试这样的事情：

WITH all_orders AS (
    SELECT YEAR(Order_Date) 'Year', COUNT(*) 'Count'
    FROM [targil 2].[dbo].[exc2 - orders]
    GROUP BY YEAR(Order_Date)
), men_orders AS (
    SELECT YEAR(o.Order_Date) 'Year', COUNT(*) 'Count'
    FROM [targil 2].[dbo].[exc2 - orders] o
    JOIN [targil 2].[dbo].[exc2 - product] p
        ON p.Product_ID = o.Product_ID
    WHERE p.Product_Name LIKE '%men%'
    GROUP BY YEAR(o.Order_Date)
)
SELECT
    COALESCE(a.Year, m.Year) 'Year',
    a.Count 'All Count',
    m.Count 'Men Count',
    CAST(ISNULL(m.Count, 0) AS DECIMAL) / CAST(a.Count AS DECIMAL) 'Ratio'
FROM all_orders a
LEFT JOIN men_orders m ON m.Year = a.Year

推荐阅读