如何基于其他表选择id的计数 [英] how do select count of id Based on other table

问题描述

大家好
我在Google和其他网站上搜索了自己的问题，但无法获得任何解决方案.
请仔细回答(谢谢)
请首先查看下图(数据库图)，然后阅读我的问题
图片地址:
http://eil.net84.net/photos/6997f04a7db9.jpg [

Hi all
I searched in Google and other sites for my problem but can''t get any solution for it.
Please answer carefully (Thanks)
Please at first see the image below ( DB Diagram) and then read my question
Image Address :
http://eil.net84.net/photos/6997f04a7db9.jpg[^]

I want select count of this Id based on Product and Sell-Main-Details Table :

color_id
size_id
model_id
group_id

基于产品和主要销售明细表

也:
根据卖方主要明细表计算 Product id，cus_id

例子1:
color_id = 1 , 2 , 5
count of it on sell-main-details= 15 , 5 , 13


例子2:
cus_id =1,2,3
count_cus_id in Sell-Main-Details Table =81,11,51


非常感谢我的朋友们.

Based On Product And Sell-Main-Details Tables

Also :
count Product id , cus_id Based on Sell-Main-Details Table

example 1 :
color_id = 1 , 2 , 5
count of it on sell-main-details= 15 , 5 , 13


example 2 :
cus_id =1,2,3
count_cus_id in Sell-Main-Details Table =81,11,51


Thanks A Lot My Friends

推荐答案

我看不到图表上数据库的完整结构，因为只有表格的一部分可见.

以下示例显示如何获取每个product_id的xxxx_id计数:

I can''t see the full structure of database on your diagram, because only the parts of tables are visible.

The following example shows how to get count of xxxx_id for each product_id:

SELECT DT.*
FROM (
    SELECT 'color_id' AS [Item], [smd_product_id], [color_id] AS [TypeOfItem], COUNT([color_id]) AS [CountOfItem]
    FROM Sell-Main-Details
    GROUP BY [smd_product_id], [color_id]
    UNION ALL
    SELECT 'size_id' AS [Item], [smd_product_id], [size_id] AS [TypeOfItem], COUNT([size_id]) AS [CountOfItem]
    FROM Sell-Main-Details
    GROUP BY [smd_product_id], [size_id]
    UNION ALL
    SELECT 'model_id' AS [Item], [smd_product_id], [model_id] AS [TypeOfItem], COUNT([model_id]) AS [CountOfItem]
    FROM Sell-Main-Details
    GROUP BY [smd_product_id], [model_id]
    UNION ALL
    SELECT 'group_id' AS [Item], [smd_product_id], [group_id] AS [TypeOfItem], COUNT([group_id]) AS [CountOfItem]
    FROM Sell-Main-Details
    GROUP BY [smd_product_id], [group_id]
    UNION ALL
    SELECT 'cus_id' AS [Item], [smd_product_id], [smd_cus_id] AS [TypeOfItem], COUNT([smd_cus_id]) AS [CountOfItem]
    FROM Sell-Main-Details
    GROUP BY [smd_product_id], [cus_id]) AS DT
ORDER BY DT.[Item], DT.[TypeOfItem]

这篇关于如何基于其他表选择id的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！