现有一表如下
NUM(号码) ZS(总数) WZ(位置) TIME(时间戳) NEIRONG(内容)
111 3 1 03101050 我想
111 3 2 03101050 吃
222 4 4 03101050 人
111 3 3 03101050 饭
222 4 2 03101050 是
111 2 1 03101150 打
222 4 3 03101050 中国
222 4 1 03101050 我
111 2 2 03101150 酱油了如何做查询才能将分开的句子组合起来,并生成新表
NUM(号码) ZS(总数) TIME(时间戳) NEIRONG(内容)
111 3 03101050 我想 吃 饭
111 2 03101150 打 酱油了
222 4 03101050 我 是 中国 人
NUM(号码) ZS(总数) WZ(位置) TIME(时间戳) NEIRONG(内容)
111 3 1 03101050 我想
111 3 2 03101050 吃
222 4 4 03101050 人
111 3 3 03101050 饭
222 4 2 03101050 是
111 2 1 03101150 打
222 4 3 03101050 中国
222 4 1 03101050 我
111 2 2 03101150 酱油了如何做查询才能将分开的句子组合起来,并生成新表
NUM(号码) ZS(总数) TIME(时间戳) NEIRONG(内容)
111 3 03101050 我想 吃 饭
111 2 03101150 打 酱油了
222 4 03101050 我 是 中国 人
111 3 2 03101050 吃
111 3 3 03101050 饭NUM相同的 按WZ的顺序组合。
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+' '+cast(NEIRONG as varchar(100)) from lc where NUM=@num and ZS=@zs order by NUM,WZ
return stuff(@sql,1,1,'')
end
GOselect NUM,ZS,TIME,dbo.fun(NUM,ZS) as NEIRONG from lc group by NUM,ZS,TIME
drop function dbo.fun
--我在Sql2005数据库测试通过
declare @t table(num int,zs int,wz int,[time] varchar(8),neirong varchar(50))
insert into @t
select 111,3,1,'03101050','我想' union all
select 111,3,2,'03101050','吃' union all
select 222,4,4,'03101050','人' union all
select 111,3,3,'03101050','饭' union all
select 222,4,2,'03101050','是' union all
select 111,2,1,'03101150','打' union all
select 222,4,3,'03101050','中国' union all
select 222,4,1,'03101050','我' union all
select 111,2,2,'03101150','酱油了'select num,zs,[time],stuff((select ','+neirong from @t where num=t.num and zs=t.zs order by wz for xml path('') ),1,1,'') as neirong
from @t t
group by num,zs,[time]--结果
--num --zs --[time] --neirong
111 2 03101150 打,酱油了
111 3 03101050 我想,吃,饭
222 4 03101050 我,是,中国,人
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void Form1_Load(object sender, EventArgs e)
{
string txtconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\database\\database.mdb";
OleDbConnection conn = new OleDbConnection(txtconn);
conn.Open();
DataSet ds=new DataSet ();
// string cmdtxt="SELECT RecInfo.Tel, RecInfo.Mes FROM RecInfo WHERE RecInfo.Mes LIKE 'L*'";
string cmdtxt = "SELECT RecInfo.Tel, RecInfo.Mes FROM RecInfo where Mes like 'L%'";
OleDbDataAdapter da = new OleDbDataAdapter(cmdtxt, conn);
da.Fill(ds, "longrev");
dataGridView1.DataSource = ds.Tables ["longrev"];
OleDbCommand cmdrd = new OleDbCommand(cmdtxt, conn);
OleDbDataReader dr=cmdrd .ExecuteReader ();
while (dr.Read()) //L#5#5#03111012#中国人 {
string nan = dr[1].ToString();
string[] fenjie = nan.Split('#');
string fstel = dr[0].ToString();
string zt = fenjie[1];
string djt = fenjie[2];
string datatime = fenjie[3];
string mes = fenjie[4];
string cmdinsertext = "insert into revlong (fstel,zt,djt,mes,datatime) values ('" + fstel + "','" + zt + "','" + djt + "','" + mes + "','" + datatime + "')";
OleDbCommand cmdins = new OleDbCommand(cmdinsertext, conn);
cmdins.ExecuteNonQuery();
}
//TODO:显示新拆分的长短信表
string cmdtxt2 = "SELECT * FROM revlong ";
OleDbDataAdapter da2 = new OleDbDataAdapter(cmdtxt2, conn); da2.Fill(ds, "ooo");
dataGridView2.DataSource = ds.Tables["ooo"]; //TODO:将任意一整条长短信写入TEM临时表,同时在长短信表revlong中删除这一长短信
string writechang = "select top 1 * into TEM from revlong where revlong.djt='1'";
OleDbCommand cmdwrite = new OleDbCommand(writechang, conn);
cmdwrite.ExecuteNonQuery(); string cmdw1 = "select * from TEM";
DataTable drw1 = new DataTable();
OleDbDataAdapter da3 = new OleDbDataAdapter(cmdw1, conn);
da3.Fill(drw1);
string m = drw1.Rows[0]["zt"].ToString();
// int n = Convert.ToInt32(drw1.Rows [0].ItemArray[2].ToString ());
int n = Convert.ToInt32(m ); for (int i = 2; i <= n; i++)
{ string ins = "insert into TEM (fstel,zt,djt,datatime,mes) select fstel,zt,djt,datatime,mes from revlong where fstel='" + drw1.Rows[0].ItemArray[1].ToString() + "'and datatime='" + drw1.Rows[0].ItemArray[6].ToString() + "'and djt='" + i.ToString() + "'";
OleDbCommand cmdw2 = new OleDbCommand(ins, conn);
cmdw2.ExecuteNonQuery();
}
}
}
}