SELECT count( 0 ) FROM ( SELECT *, mt.product_id AS productId FROM mrp_tryout mt WHERE mt.company_id = 1 GROUP BY mt.product_id ) table_count
时间: 2024-04-10 12:30:51 浏览: 22
您的问题是:查询一个表中不重复的产品数量吗?
如果是的话,您可以使用以下SQL语句:
SELECT COUNT(0)
FROM (
SELECT *, mt.product_id AS productId
FROM mrp_tryout mt
WHERE mt.company_id = 1
GROUP BY mt.product_id
) AS table_count;
相关问题
优化这条sql: select distinct (select product_name from t_product from where id = #{productId} and mark = 1 and status = 1) as productName, (select count(0) from t_clue a where a.distribution_status != 4 and a.mark = 1 and a.product_id = #{productId} and a.status in(1,2,3,31,32,33)) as clueCount, (select count(0) from t_clue a left join t_clue_appendix b on a.clue_code = b.clue_code where a.distribution_status != 4 and a.mark = 1 and b.file_url is not null and a.product_id = #{productId} and a.status in (3,31,32,33)) as intentionCount, (select count(0) from t_clue a where a.status in (4,5,7,8) and a.distribution_status != 4 and a.mark = 1 and a.product_id = #{productId} and a.status = 4) as incomingCount, (select count(0) from t_clue a where a.status in (5,7,8) and a.distribution_status != 4 and a.mark = 1 and a.product_id = #{productId} and a.status in (5,7,8)) as approvedCount, (select count(0) from t_clue a where a.status = 6 and a.distribution_status != 4 and a.mark = 1 and a.product_id = #{productId} and a.status = 6) as rejectionCount, (select count(0) from t_clue a where a.status in (7,8) and a.distribution_status != 4 and a.mark = 1 and a.product_id = #{productId}) as loanCount, (select count(0) from t_clue a where a.status = 8 and a.distribution_status != 4 and a.mark = 1 and a.product_id = #{productId}) as swipeCount, (select sum(a.loan_amount) from t_clue a where a.distribution_status != 4 and a.mark = 1 and a.product_id = #{productId}) as loanMoney, (select sum(a.use_amount) from t_clue a where a.distribution_status != 4 and a.mark = 1 and a.product_id = #{productId}) as swipeMoney
可以考虑使用 JOIN 语句替换子查询,同时对查询中的字段添加索引以提高查询效率。修改后的 SQL 可能如下所示:
```
SELECT
t1.product_name AS productName,
COUNT(CASE WHEN t2.status IN (1,2,3,31,32,33) THEN 1 END) AS clueCount,
COUNT(CASE WHEN t2.status IN (3,31,32,33) AND t3.file_url IS NOT NULL THEN 1 END) AS intentionCount,
COUNT(CASE WHEN t2.status = 4 THEN 1 END) AS incomingCount,
COUNT(CASE WHEN t2.status IN (5,7,8) THEN 1 END) AS approvedCount,
COUNT(CASE WHEN t2.status = 6 THEN 1 END) AS rejectionCount,
COUNT(CASE WHEN t2.status IN (7,8) THEN 1 END) AS loanCount,
COUNT(CASE WHEN t2.status = 8 THEN 1 END) AS swipeCount,
SUM(t2.loan_amount) AS loanMoney,
SUM(t2.use_amount) AS swipeMoney
FROM
t_product t1
JOIN t_clue t2 ON t1.id = t2.product_id
LEFT JOIN t_clue_appendix t3 ON t2.clue_code = t3.clue_code
WHERE
t1.id = #{productId} AND t1.mark = 1 AND t1.status = 1 AND t2.distribution_status != 4 AND t2.mark = 1
GROUP BY
t1.product_name;
```
function getPagedList(contractId) { var contractId = body.param.contractId; var sql = ` SELECT a.id, a.contract_id contractId, a.product_id productId, a.purchase_num purchaseNum, a.purchase_price purchasePrice, a.remark, b.product_name productName, b.spec_code specCode, c.type_name typeName, d.unitcode unitCode FROM mes214820519.wms_pur_list_214820519 a, mes214820519.wms_product_214820519 b, mes214820519.wms_product_class_214820519 c, mes214820519.wms_unit_code_214820519 d WHERE a.product_id=b.id AND b.product_class_id = c.id AND b.unit_code_id=d.id AND a.contract_id=#{contractId}`; return db.pager(sql, {contractId: contractId}) } 用JavaScript语言修改这段代码
function getPagedList(contractId) {
var sql = `SELECT
a.id,
a.contract_id as contractId,
a.product_id as productId,
a.purchase_num as purchaseNum,
a.purchase_price as purchasePrice,
a.remark,
b.product_name as productName,
b.spec_code as specCode,
c.type_name as typeName,
d.unitcode as unitCode
FROM
mes214820519.wms_pur_list_214820519 a
INNER JOIN mes214820519.wms_product_214820519 b
ON a.product_id = b.id
INNER JOIN mes214820519.wms_product_class_214820519 c
ON b.product_class_id = c.id
INNER JOIN mes214820519.wms_unit_code_214820519 d
ON b.unit_code_id = d.id
WHERE
a.contract_id = :contractId`;
return db.pager(sql, { contractId: contractId });
}
在修改后的代码中,我使用了ES6模板字符串来构建SQL语句,同时使用了INNER JOIN来连接多张表,使代码更加简洁易读。另外,我还使用了命名参数代替了原来的占位符,以提高代码的可读性和可维护性。