数据库语句大全之利用IN进行查询/交叉表查询

最新推荐文章于 2024-08-26 09:12:21 发布

sgdgoodboy

最新推荐文章于 2024-08-26 09:12:21 发布

阅读量3.3k

点赞数

分类专栏：数据库文章标签：数据库 sql server pivot security null 出版

本文链接：https://blog.csdn.net/sgdgoodboy/article/details/2119438

版权

数据库专栏收录该内容

28 篇文章 0 订阅

订阅专栏

1.使用IN引入子查询限定查询范围:select * from duobiao as du where 收入 in(select 收入 from duobiao where 收入>"+sed1+" and 收入<"+sed2+")

说明IN 和 NOT IN

2.用IN查询表中的记录信息:select*from kjbdsjk where 姓名 in('"+m_edit+"')

3.利用TRANSFORM分析数据:transform sum(销售数量) select 语言类别 from jiaochabiao group by 语言类别 pivot 销售时间

说明交叉表可以清晰地反映数据之间的关系．在使用Access进行交叉查询时，可以使用TRANSFORM语句，pivot表示字段

4.利用TRANSFORM动态分析数据:transform sum("+str1+") select "+str2+" from jiaochabiao group by "+str2+" pivot "+str3+"

5.静态交叉表(SQL Server 2000):select 姓名, sum(case 部门 when '食品部' then 业绩 else null end) as [食品部业绩],sum(case 部门 when '家电部' then 业绩 else null end) as [家电部业绩],sum(case 部门 when '服装部' then 业绩 else null end) as [服装部业绩] from xsbiao group by 姓名

6.动态交叉表(SQL Server 2000):自己分析吧：

void CDynamicDlg::OnButquery()
{
// TODO: Add your control notification handler code here
m_Grid.DeleteAllItems();
for(int k=0;k<num;k++)
  m_Grid.DeleteColumn(0);
CString name1,name2,name3;
m_com1.GetWindowText(name1);
m_com2.GetWindowText(name2);
m_com3.GetWindowText(name3);
if(name1.IsEmpty() || name2.IsEmpty() || name3.IsEmpty())
{
  MessageBox("");
  return;
}
m_pConnection.CreateInstance("ADODB.Connection");
_bstr_t strConnect="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=shujuku;Data Source=.";
m_pConnection->Open(strConnect,"","",adModeUnknown);
m_pRecordset.CreateInstance(__uuidof(Recordset));

CString str,name[10];
int i=2,j=0;
if(name2 == "书名")
{
  str = "select distinct 书名 from chongfujilu order by 书名";
  m_pRecordset->Open((_bstr_t)str,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
  while(!m_pRecordset->adoEOF)
  {
   name[i] = (char*)(_bstr_t)m_pRecordset->GetCollect("书名");
   m_pRecordset->MoveNext();
   i++;
  }
}
else
{
  str = "select distinct 出版社 from chongfujilu order by 出版社";
  m_pRecordset->Open((_bstr_t)str,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
  while(!m_pRecordset->adoEOF)
  {
   name[i] = (char*)(_bstr_t)m_pRecordset->GetCollect("出版社");
   m_pRecordset->MoveNext();
   i++;
  }
}
name[0] = name3;
m_Grid.InsertColumn(0,name[0],LVCFMT_LEFT,100,0);
name[1] = name1;
m_Grid.InsertColumn(1,name[1],LVCFMT_LEFT,100,1);
for(j=0;j<i-2;j++)
{
  m_Grid.InsertColumn(j+2,name[j+2],LVCFMT_LEFT,100,j+2);
}
m_pRecordset->Close();

str.Format("Execute scorss @strCol='%s',@strGroup='%s',@strNumber='%s'",name2,name3,name1);
m_pRecordset->Open((_bstr_t)str,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
while(!m_pRecordset->adoEOF)
{
  CString str1;
  m_Grid.InsertItem(0,"");
  for(j=0;j<i;j++)
  {
   m_Grid.SetItemText(0,j,(char*)(_bstr_t)m_pRecordset->GetCollect((_variant_t)name[j]));
  }
  m_pRecordset->MoveNext();
}
if(m_pRecordset!=NULL)
  m_pRecordset->Close();
m_pConnection->Close();
num = i;
}