SQL不重复查找数据及把一列多行内容拼成一行
如下表：
表名：Test
ID RowID Col1 Col2 1 1 A A 2 1 B A 3 1 A B 4 1 C B
1,查找表中字段重复的只查找一次
select distinct Col1 from Test ;
select Col1 from Test where ID in(select min(ID) from Test group by Col1 );
结果为：
A
B
C
2，统计并查询该字段出现的数量
SELECT Col1,COUNT(Col1) FROM Test GROUP BY Col1;
结果为：
A 2
B 1
C 1
3，把一列多行内容拼成一行
select STUFF ((select ','+F.Col1 from Test F where F.RowID= 1 order by F.ID FOR XML PATH('')),1,1,'') As Col3;
结果为：
Col3
A,B,A,C
转载于:https://www.cnblogs.com/cjm123/p/8564194.html
/// <summary> /// 查找重复值(多列) /// </summary> /// <param name="colNums"></param> /// <returns></returns> public string FindRepeat(int[] colNums) { List<string> ls = new List<string>(); Excel.Range rng = Sht.UsedRange; int startLine = rng.CurrentRegion.Rows[1].Row; int endLine = rng.CurrentRegion.Rows[rng.CurrentRegion.Rows.Count].Row; if (endLine > startLine) { List<object[,]> listObjs = new List<object[,]>(); foreach (int col in colNums) { object[,] objs = Sht.Range[Sht.Cells[startLine, col], Sht.Cells[endLine, col]].Value2; listObjs.Add(objs); } Dictionary<string, List<int>> dict = new Dictionary<string, List<int>>(); for (int i = listObjs[0].GetLowerBound(0); i <= listObjs[0].GetUpperBound(0); i++) { List<string> lsLine = new List<string>(); for (int j = 0; j < listObjs.Count; j++) { string c = (null == listObjs[j][i, 1]) ? "" : listObjs[j][i, 1].ToString().Trim(); lsLine.Add(string.Format("[{0}{1}]{2}", colNums[j], MyCommon.IntToLetter(colNums[j]),c)); } string k = string.Join("/",lsLine.ToArray()); if (!dict.ContainsKey(k)) { List<int> rows = new List<int>(); rows.Add(startLine - 1 + i); dict.Add(k, rows); } else { dict[k].Add(startLine - 1 + i); } } foreach (var item in dict) { if (item.Value.Count > 1) { ls.Add(string.Format("【{0}】:【{1}】", item.Key, string.Join("/", item.Value.ToArray()))); } } } //ls.Add(string.Format("所在列:{0},开始行号:{1} 结束行号:{2}",col,startLine,endLine)); return string.Join(Environment.NewLine, ls.ToArray()); }调用:
/// <summary> /// 查找重复项 /// </summary> /// <param name="control"></param> public void FindRepeat(IRibbonControl control) { Excel.Application app = ExcelDnaUtil.Application as Excel.Application; if (!Information.TypeName(app.Selection).Equals("Range")) return; Excel.Range rngSelection = app.Selection; HashSet<int> colNums = new HashSet<int>(); foreach (Excel.Range item in rngSelection.Areas) { colNums.Add(item.Column); } List<int> colNumList = new List<int>(colNums); colNumList.Sort(); //string msg = string.Format("{0}",string.Join("/", colNumList.ToArray())); //MessageBox.Show(msg); string msg = string.Empty; MyAided myAided = new MyAided(app.ActiveSheet as Excel.Worksheet); if (1 == colNumList.Count) { msg = myAided.FindRepeat(); } else { msg = myAided.FindRepeat(colNumList.ToArray()); } if (!string.IsNullOrEmpty(msg)) { MessageBox.Show(msg, "重复项", MessageBoxButtons.OK); MyCommon.WriteLog(msg, "d:\\Log", true); } }
以下例子由单列变成多行多列为例，单行数据操作相同
原始数据：
结果数据：
过程操作：
1、在目标单元格里输入原始数据的单元格号，先输入A1,A2，然后选中光标变成实体+字的时候，右拉填充，然后再下来填充，变成
2、选中上面的目标单元格，是查找替代功能，将A替换成=A。
3、这个时候已经实现数据的格式转换，但是目标数据和原始数据还有关联关系，相当于是用=A1这种公式来显示A1单元格的数据，如果直接拷贝到其他单元格，会出错，需要拷贝，右键“选择性粘贴”，选“数值”就可以了。
