此分类汇总想改成in来完成，请高手帮忙

[yuhouqingchen2020]

select t.zhihangName,count(*) as rscount from (SELECT dbo.tbbank.zhihangName, dbo.tbInfoQiShiYe.QSYName
FROM dbo.tbbank INNER JOIN
dbo.tbuser ON dbo.tbbank.zhihangNo = dbo.tbuser.zhihangNo INNER JOIN
dbo.tbInfoQiShiYe ON dbo.tbuser.yonghuKey = dbo.tbInfoQiShiYe.yonghuKey) t
group by t.zhihangName

此分类汇总想改成in来完成，不想用INNER JOIN怎么写呢，请高手帮忙

[baidu_27549073]

先把表结构发出来吧

[二月十六]

改成in？这个可以简化一下

SELECT  dbo.tbbank.zhihangName ,
        COUNT(*) AS rscount
FROM    dbo.tbbank
        INNER JOIN dbo.tbuser ON dbo.tbbank.zhihangNo = dbo.tbuser.zhihangNo
        INNER JOIN dbo.tbInfoQiShiYe ON dbo.tbuser.yonghuKey = dbo.tbInfoQiShiYe.yonghuKey
GROUP BY tbbank.zhihangName

[中国风]

你这类情况直接用INNER JOIN 写法高效一些，你用到了三个表关联记录数 写T-SQL要学会用别名让你的代码更简洁 如：

SELECT  a.zhihangName ,
        COUNT(*) AS rscount,--总记录数
		COUNT(DISTINCT c.QSYName)-- QSYName的记录数过滤相同的QSYName
FROM    dbo.tbbank AS a
        INNER JOIN dbo.tbuser AS b ON a.zhihangNo = b.zhihangNo
        INNER JOIN dbo.tbInfoQiShiYe AS c ON b.yonghuKey = c.yonghuKey
GROUP BY a.zhihangName;

[yuhouqingchen2020]

因为我用ssh的hql，所以 INNER JOIN 不能用，想用in来完成

[zhouyuehai1978]

SELECT t.zhihangName,
       COUNT(*)  AS rscount
FROM   (
           SELECT dbo.tbbank.zhihangName,
                  (
                      SELECT dbo.tbInfoQiShiYe.QSYName
                      FROM   dbo.tbInfoQiShiYe
                      WHERE  dbo.tbInfoQiShiYe.yonghuKey IN (SELECT dbo.tbuser.yonghuKey
                                                             FROM   dbo.tbuser
                                                             WHERE  dbo.tbbank.zhihangNo = dbo.tbuser.zhihangNo)
                  ) AS QSYName
           FROM   dbo.tbbank
       )            t
GROUP BY
       t.zhihangName

[zhouyuehai1978]

又改了下，用这个

SELECT t.zhihangName,
       COUNT(*)  AS rscount
FROM   (
           SELECT dbo.tbbank.zhihangName,
                  (
                      SELECT dbo.tbInfoQiShiYe.QSYName
                      FROM   dbo.tbInfoQiShiYe
                      WHERE  dbo.tbInfoQiShiYe.yonghuKey IN (SELECT dbo.tbuser.yonghuKey
                                                             FROM   dbo.tbuser
                                                             WHERE  dbo.tbbank.zhihangNo = dbo.tbuser.zhihangNo)
                  ) AS QSYName
           FROM   dbo.tbbank
       )            t
WHERE  t.QSYName IS NOT NULL
GROUP BY
       t.zhihangName

[二月十六]

引用 3 楼 ybsybsybsybs 的回复:

因为我用ssh的hql，所以 INNER JOIN 不能用，想用in来完成

试试这个：

SELECT  dbo.tbbank.zhihangName ,
        COUNT(*) AS rscount
FROM    dbo.tbbank
WHERE   dbo.tbbank.zhihangNo IN (
        SELECT  dbo.tbuser.zhihangNo
        FROM    dbo.tbuser
        WHERE   dbo.tbuser.yonghuKey IN (
                SELECT  dbo.tbInfoQiShiYe.yonghuKey
                FROM    dbo.tbInfoQiShiYe ) )
GROUP BY tbbank.zhihangName

[中国风]

这样？没记录时显示0

   SELECT 
	   a.zhihangName,(SELECT COUNT(*) FROM dbo.tbuser AS b INNER JOIN dbo.tbInfoQiShiYe AS c ON b.yonghuKey = c.yonghuKey WHERE   a.zhihangNo = b.zhihangNo) AS rscount
	   FROM dbo.tbbank as a

过滤0

SELECT  zhihangName ,
        rscount
FROM    ( SELECT    a.zhihangName ,
                    ( SELECT    COUNT(*)
                      FROM      dbo.tbuser AS b
                                INNER JOIN dbo.tbInfoQiShiYe AS c ON b.yonghuKey = c.yonghuKey
                      WHERE     a.zhihangNo = b.zhihangNo
                    ) AS rscount
          FROM      dbo.tbbank AS a
        ) AS t
WHERE   rscount > 0;

[吉普赛的歌]

引用 3 楼 ybsybsybsybs 的回复:

因为我用ssh的hql，所以 INNER JOIN 不能用，想用in来完成

不是每个 sql 都能转 hql ， 实在转不了的， 还是老老实实用 sql + jdbc 吧。 就算能转成你要的， 转了之后效率高么？ 在实际工作中， 性能是第一位的， 不要太拘泥于做法。