有三张表
职工档案表
ZGDA(ZGBH,ZGXM)角色表
ROLE(ROLEID,ROLENAME)职工角色表
ZGJS(ZGBH,ROLEID),一个职工有多个角色
程序中,想通过GRID实现,第一行是角色,第一列是人员,如下
角色1 角色2 角色2 角色*
人员1 Y Y
人员2 Y Y
人员3点击单元格时在 职工角色表 里插入一条数据,同时单元阁显示Y,Y代表有此角色大家是如何实现的,在线等待,马上送分
职工档案表
ZGDA(ZGBH,ZGXM)角色表
ROLE(ROLEID,ROLENAME)职工角色表
ZGJS(ZGBH,ROLEID),一个职工有多个角色
程序中,想通过GRID实现,第一行是角色,第一列是人员,如下
角色1 角色2 角色2 角色*
人员1 Y Y
人员2 Y Y
人员3点击单元格时在 职工角色表 里插入一条数据,同时单元阁显示Y,Y代表有此角色大家是如何实现的,在线等待,马上送分
procedure TForm1.FormShow(Sender: TObject);
var
row,col:integer; //Grid的行列
begin
row:=1; col:=1;
//生成Grid行信息
ADODataSet1.Close;
ADODataSet1.CommandText:='Select count(*) as RYS from zgda';
ADODataSet1.Open;
StringGrid1.RowCount:=ADODataSet1.FieldByName('RYS').AsInteger+1;
ADODataSet1.Close;
ADODataSet1.CommandText:='Select ZGXM from zgda order by zgxm';
ADODataSet1.Open;
While Not ADODataSet1.Eof do
begin
StringGrid1.Cells[0,row]:=ADODataSet1.FieldByName('ZGXM').AsString;
row:=row+1;
ADODataSet1.Next;
end;
//生成Grid列信息
ADODataSet1.Close;
ADODataSet1.CommandText:='Select count(*) as JSS from role';
ADODataSet1.Open;
StringGrid1.RowCount:=ADODataSet1.FieldByName('JSS').AsInteger+1;
ADODataSet1.Close;
ADODataSet1.CommandText:='Select ROLENAME from role order by rolename';
ADODataSet1.Open;
While Not ADODataSet1.Eof do
begin
StringGrid1.Cells[col,0]:=ADODataSet1.FieldByName('ROLENAME').AsString;
col:=col+1;
ADODataSet1.Next;
end;
//生成Grid角色信息
for row:=1 to StringGrid1.RowCount do
begin
ADODataSet1.Close;
ADODataSet1.CommandText:='SELECT ROLE.ROLENAME FROM ZGJS INNER JOIN ZGDA ON ZGJS.ZGBH = ZGDA.ZGBH INNER JOIN ROLE ON ZGJS.ROLEID = ROLE.ROLEID WHERE ZGDA.ZGXM = '+Quotedstr(StringGrid1.Cells[0,row]);
ADODataSet1.Open;
While not ADODataSet1.Eof do
begin
for col:=1 to StringGrid1.ColCount do
if ADODataSet1.FieldByName('rolename').AsString = StringGrid1.Cells[col,0] then
StringGrid1.Cells[col,row]:='Y';
ADODataSet1.Next;
end;
end;end;
insert @ZGDA select '01', 'ourlin'
union all select '02', 'gladlin'
union all select '03', 'ldx'
union all select '04', 'ook'
DECLARE @ROLE TABLE(ROLEID varchar(2), ROLENAME varchar(20))
insert @ROLE select 'A1', 'ROLE1'
union all select 'A2', 'ROLE2'
union all select 'A3', 'ROLE3'
union all select 'A4', 'ROLE4'
union all select 'A5', 'ROLE5'DECLARE @ZGJS TABLE(ZGBH varchar(2), ROLEID varchar(2))
insert @ZGJS select '01', 'A2'
UNION ALL SELECT '02', 'A1'
UNION ALL SELECT '04', 'A3'
UNION ALL SELECT '02', 'A5'
UNION ALL SELECT '03', 'A4'
DECLARE @s nvarchar(4000)drop table #btemp
select a.ZGBH, b.ZGXM, a.ROLEID, c.ROLENAME into #btemp
from @ZGJS as a
left join @ZGDA as b ON a.ZGBH=b.ZGBH
left join @ROLE as c ON a.ROLEID=c.ROLEIDselect * from #btempset @s='select ZGBH '
select @s=@s
+','+quotename(ROLEID)
+N'=Case ROLEID WHEN '+quotename(a.ROLEID, N'''')
+N' THEN ''Y'' END'
FROM #btemp as a set @s=@s+N' FROM #btemp order by ZGBH'
Exec(@s)