SQL中from后两个子查询的连接方式。

**

SQL中from后两个子查询的连接方式

**

正确的：-------------------------------------------------------------------

ALTER PROCEDURE [dbo].[DM_GZBJQYHZ_HYFL]
@BIXNDW_DWJC AS VARCHAR(20),
@SJ AS INT
AS
BEGIN
SELECT  DISTINCT
   CASE 
      WHEN @BIXNDW_DWJC = '省国资委' THEN f.SL 
      ELSE g.SL 
   END AS 数量,
   CASE 
      WHEN @BIXNDW_DWJC = '省国资委' THEN f.HYZD_MC 
      ELSE g.HYZD_MC 
   END AS 行业名称
FROM
   (
      SELECT COUNT(a.SSHY) AS SL, O.HYZD_MC AS HYZD_MC
      FROM (
         SELECT SUBSTRING(QYJCXX_SSHY, 1, 4) AS SSHY
         FROM DM_GZBJQYHZ
         WHERE YEAR(QYJCXX_CLSJ) >= 1958 AND YEAR(QYJCXX_CLSJ) <= @SJ
      ) a
      LEFT JOIN ODS_ZHJC_ZBYZ_CQBD_JQHYZD O ON O.HYZD_BH = a.SSHY
      GROUP BY O.HYZD_MC
   ) AS f
   FULL OUTER JOIN 
   (
      SELECT  COUNT(a.SSHY) AS SL, OD.HYZD_MC AS HYZD_MC
      FROM (
         SELECT SUBSTRING(QYJCXX_SSHY, 1, 4) AS SSHY
         FROM DM_GZBJQYHZ D
         INNER JOIN ODS_BIXNDW_DWJC p ON D.name = p.BIXNDW_DWMC and p.BIXNDW_DWJC = @BIXNDW_DWJC 
         WHERE  YEAR(QYJCXX_CLSJ) >= 1958 AND YEAR(QYJCXX_CLSJ) <= @SJ
      ) a
      LEFT JOIN ODS_ZHJC_ZBYZ_CQBD_JQHYZD OD ON OD.HYZD_BH = a.SSHY
      GROUP BY OD.HYZD_MC
   ) AS g ON 1=1   -- This join condition is just to have a join condition, you might need to adjust it based on your actual requirement
ORDER BY 数量 DESC
END

错误的：------------------------------------------------------------------

ALTER PROCEDURE [dbo].[DM_GZBJQYHZ_HYFL] @BIXNDW_DWJC AS VARCHAR ( 20 ), @SJ AS INT AS BEGIN
	SELECT DISTINCT
	CASE
			
		WHEN
			@BIXNDW_DWJC = '省国资委' THEN
				f.SL ELSE g.SL 
				END AS 数量,
		CASE
				
				WHEN @BIXNDW_DWJC = '省国资委' THEN
				f.HYZD_MC ELSE g.HYZD_MC 
			END AS 行业名称 
		FROM
			(
			SELECT COUNT
				( a.SSHY ) AS SL,
				O.HYZD_MC AS HYZD_MC 
			FROM
				(
				SELECT SUBSTRING
					( QYJCXX_SSHY, 1, 4 ) AS SSHY 
				FROM
					DM_GZBJQYHZ 
				WHERE
					YEAR ( QYJCXX_CLSJ ) >= 1958 
					AND YEAR ( QYJCXX_CLSJ ) <= @SJ 
				) a
				LEFT JOIN ODS_ZHJC_ZBYZ_CQBD_JQHYZD O ON O.HYZD_BH = a.SSHY 
			GROUP BY
				O.HYZD_MC 
			) AS f,----这个地方
			(
			SELECT COUNT
				( a.SSHY ) AS SL,
				OD.HYZD_MC AS HYZD_MC 
			FROM
				(
				SELECT SUBSTRING
					( QYJCXX_SSHY, 1, 4 ) AS SSHY 
				FROM
					DM_GZBJQYHZ D
					INNER JOIN ODS_BIXNDW_DWJC p ON D.name = p.BIXNDW_DWMC 
					AND p.BIXNDW_DWJC = @BIXNDW_DWJC 
				WHERE
					YEAR ( QYJCXX_CLSJ ) >= 1958 
					AND YEAR ( QYJCXX_CLSJ ) <= @SJ 
				) a
				LEFT JOIN ODS_ZHJC_ZBYZ_CQBD_JQHYZD OD ON OD.HYZD_BH = a.SSHY 
			GROUP BY
				OD.HYZD_MC 
			) AS g 
		ORDER BY
		数量 DESC 
	END ```

困扰了一天。。。。。。。。。。。。。。。。。。。。。。。。。

当时的矛盾点是：当传参 为 省国资委时 数据出不来，子查询都正确，，错误原因使用逗号，让fg产生了笛卡尔积

经过多次查询，最后在文心遗言中找到了答案，

f与g两个子查询之间“，”，这样连接好像不对，

最后换成了 FULL OUTER JOIN （----）g on 1=1,就好使了，具体原因还要查查。

总结起来，如果在"f"和"g"之间仅使用逗号隔开而没有其他连接条件，查询将返回它们的笛卡尔积，而不是你想要的结果。为了得到正确的结果，你需要使用适当的连接条件来连接这两个表或列。