MySQL - 查找给定的同一个表中有多少重复项

Considering I have the following two sets of rows (same type) in a WHERE clause:

考虑到WHERE子句中有以下两组行（相同类型）：

I need to find how many A is in B For example, for the given table above, it would be 66% since 2 out of 3 numbers are in B

我需要找到B中有多少A例如，对于上面给定的表，它将是66％，因为3个数中有2个在B中

Another example:

另一个例子：

Would give 100% since all of the numbers in A are in B

因为A中的所有数字都在B中，所以会给出100％

Here is what I tried myself: (Doesn't work on all test cases..)

这是我自己尝试的:(不适用于所有测试用例..）

DROP PROCEDURE IF EXISTS getProductsByDate;
DELIMITER //
CREATE PROCEDURE getProductsByDate (IN d_given date)
BEGIN
SELECT 
    Product,
    COUNT(*) AS 'total Number',
    (SELECT 
            (SELECT COUNT(DISTINCT Part) FROM products WHERE Product=B.Product) - COUNT(*)
        FROM
            products AS b2
        WHERE
            b2.SOP < B.SOP AND b2.Part != B.Part) AS 'New Parts',
             CONCAT(round((SELECT 
            (SELECT COUNT(DISTINCT Part) FROM products WHERE Product=B.Product) - COUNT(*)
        FROM
            products AS b2
        WHERE
            b2.SOP < B.SOP AND b2.Part != B.Part)/count(DISTINCT part)*100, 0), '%') as 'Share New'
FROM
    products AS B
WHERE
    b.SOP < d_given
GROUP BY Product;

END//

DELIMITER ;
CALL getProductsByDate (date("2018-01-01"));

Thanks.

谢谢。

1 个解决方案

#1

Naming your tables TA and TB respectively you could try something like this (test made on MSSQL and Mysql at moment)

分别命名你的表TA和TB你可以尝试这样的东西（在MSSQL和Mysql上进行测试）

SELECT ROUND(SUM(PERC) ,4)AS PERC_TOT
FROM (
    SELECT DISTINCT TA.ID , 1.00/ (SELECT COUNT(DISTINCT ID) FROM TA) AS PERC
    FROM TA 
    WHERE EXISTS ( SELECT DISTINCT ID FROM TB  WHERE TB.ID=TA.ID)
    ) C;

Output with your first sample data set:

使用您的第一个样本数据集输出：

PERC_TOT
0,6667

Output with your second sample data set:

使用第二个样本数据集输出：

PERC_TOT
1,0000

Update (I made the original for two tables, as I was thinking at solution). This is for one single table (is almost the same than the former query): (I used ID1 for column A and ID2 for column B)

更新（我为两个表创建原文，因为我正在考虑解决方案）。这是针对一个单独的表（与前一个查询几乎相同）:(我将ID1用于A列，ID2用于B列）

SELECT ROUND(SUM(PERC) ,4)AS PERC_TOT
FROM (
    SELECT DISTINCT TA.ID1 , 1.00/ (SELECT COUNT(DISTINCT ID1) FROM TA) AS PERC
    FROM TA 
    WHERE EXISTS ( SELECT DISTINCT ID2 FROM TA AS TB  WHERE TB.ID2=TA.ID1)
    ) C;

#1