C# と VB.NET の質問掲示板

SQLServer で処理を行っています。
以下のようなデータ（左側）が順不同で登録されている場合
3列目には1列目で区分される最大個数をいれて取得したいのですが
どうすればよいのでしょうか

A,1→A,1,3
A,2→A,2,3
A,3→A,3,3
B,1→B,1,2
B,2→B,2,2
C,1→C,1,4
C,2→C,2,4
C,3→C,3,4
C,4→C,4,4

こんな感じ。

SELECT T1.[col1]
      ,T1.[col2]
	  ,
	  (
		select count(T2.col1) from [test].[dbo].[table1] as T2 where T2.col1 = T1.col1
	  ) as col3
  FROM [test].[dbo].[table1] as T1

すみません。そうですよね。

＝T1＝
A,1
A,2
：
C,4

を求めるのに長いSQL（※1）を書いたので
以下のようにめっちゃ長いＳＱＬ（と言っても80行ほどですが）に
するのがいやで何か方法がないかと思って質問しました。

SELECT　col1, col2,
　　　　(
　　　　　select count(T2.col1)
　　　　　from ※1 as T2
　　　　　where T2.col1 = T1.col1
　　　　) as col3
FROM　　(
　　　　長文SQL（※1）
　　　　) as T1

最終的にはやはり同じものを2か所に書くのはいやなので
以下のように一時ファイルを作成して処理させようと思いますが、
みなさんならどうされますか？

SELECT　*
INTO　　#T1
FROM　　(
　　　　※1
　　　　);

SELECT　col1, col2,
　　　　(
　　　　　select count(T2.col1)
　　　　　from #T1 as T2
　　　　　where T2.col1 = T1.col1
　　　　) as col3
FROM　　#T1

WITH temp AS (※1)
SELECT　col1, col2,
　　　　(
　　　　　select count(T2.col1)
　　　　　from temp as T2
　　　　　where T2.col1 = T1.col1
　　　　) as col3
FROM　　temp as T1

でいいんじゃないですかね

■No89852 (ルパン さん) に返信
> 以下のようなデータ（左側）が順不同で登録されている場合
> 3列目には1列目で区分される最大個数をいれて取得したいのですが
> どうすればよいのでしょうか

自分も Hongliang さん同様、WITH 句を使う派なのですが、
今回のケースで言うと、窓関数で処理した方が手っ取り早いかも。


SELECT
  col1
, col2
, col3 = MAX(col2) OVER (PARTITION BY col1)
, col4 = COUNT(ALL col1) OVER (PARTITION BY col1)
FROM
  Table1  -- ※1