高人帮忙------------------从MSSQL移植过来的数据,碰到的两个问题------------------

Question

有两个问题,请高人帮忙...1.
是一个将部门分组,再统计不同部门的数据,总价,比例的视图...
狂晕好慢..,800多条记录查询到2分钟
数据是从MSSQL导过来的,有索引...CREATE VIEW DeptReport
AS
SELECT         DName,
                              (SELECT         COUNT(eid)
                                FROM              GetEqu
                                WHERE          Dname = a.Dname) AS Qty,
                              (SELECT         SUM(buyprice)
                                FROM              GetEqu
                                WHERE          Dname = a.Dname) AS Price, 
                              (SELECT         COUNT(eid)
                                FROM              GetEqu
                                WHERE          Dname = a.Dname) / 
                              (SELECT         COUNT(eid)
                                FROM              GetEqu) * 100 AS QtyPer,
                              (SELECT         SUM(buyprice)
                                FROM              GetEqu
                                WHERE          Dname = a.Dname) / (CASE WHEN
                              (SELECT         SUM(buyprice)
                                FROM              GetEqu) = 0 THEN 1 ELSE
                              (SELECT         SUM(buyprice)
                                FROM              GetEqu) END) * 100 AS PricePer
FROM             GetEqu a
GROUP BY  DName
2.
DELPHI平台,以前在MSSQL时可以将图片存进去,但现在不行..
字段Photo :
以前是image
现在是LONGBLOB如何改才可以用?
procedure TUpIMGForm.Button2Click(Sender: TObject);
var 
testStream:TMemoryStream; 
begin 
try 
testStream := TMemoryStream.Create;  //创建内存流 
Image1.Picture.Graphic.SaveToStream(testStream); //将图片保存至内存流中
with datam.q1 do
begin
Close;
SQL.Clear;
if (SaveType='')or (SaveType='New') then SQL.Add('Insert into Pic(Photo,class,Pcode)values(:Photo,'''+GetFileExt(edit1.Text)+''','''+Pcode+''')'); //进行插入操作
if  SaveType='Mod' then  SQL.Add('Update  Pic set Photo=:Photo,class='''+GetFileExt(edit1.Text)+''' where Pcode='''+Pcode+''' '); //进行插入操作
Parameters.ParamByName('Photo').LoadFromStream(testStream,ftBlob); //读取保存的内存图
ExecSQL;

ShowMessage('保存上传图片成功!');
    UpIMGForm.Close;
end
finally 
testStream.Free; //释放内存流 
end;

Answer 1

1
CREATE VIEW DeptReport 
AS 
SELECT         
DName, 
count(eid) as qty,
SUM(buyprice) AS Price,  
COUNT(eid)/COUNT(eid)* 100 AS QtyPer,SUM(buyprice)/ (CASE WHEN SUM(buyprice)= 0 THEN 1 ELSE SUM(buyprice) END) * 100 AS PricePer 
FROM  GetEqu a GROUP BY  DName 

Answer 2

你在mssql里把数据移植到那里去？

Answer 3

不好意思...忘记说了,是将数据库从 MSSQL 转到 MYSQL.............

Answer 4

出现错误...提示：Lost connection to MySQL server during query

Answer 5

FUNCTION JPEGSentinelsAreOK(CONST Filename: TFilename): BOOLEAN;
VAR
FileStream: TFileStream;
w1        : WORD;    // a "word" is always 2 bytes long
w2        : WORD;
BEGIN
    ASSERT(SizeOf(WORD) = 2);
    RESULT := FileExists(Filename);
    IF RESULT
    THEN
        BEGIN
        FileStream := TFileStream.Create(Filename, fmOpenRead OR fmShareDenyNone);
        TRY
        FileStream.Seek(0, soFromBeginning); // use seek or position
        FileStream.Read(w1,2);        FileStream.Position := FileStream.Size - 2;
        FileStream.Read(w2,2)
        FINALLY
        FileStream.Free
        END;
        RESULT := (w1 = $D8FF) AND (w2 = $D9FF);
    END;
    END;procedure TForm2.Button3Click(Sender: TObject);
VAR
JPEGImage:TJPEGImage;
fS : TMemoryStream;
begin
    IF OpenpictureDialog1.Execute
    THEN
    BEGIN
    IF JPEGSentinelsAreOK(OpenpictureDialog1.Filename)
    THEN
        BEGIN
        JPEGImage := TJPEGImage.Create;
        TRY
        JPEGImage.LoadFromFile(OpenPictureDialog1.Filename);
        Image1.Picture.Graphic := JPEGImage;
        try
        fs:=TMemoryStream.Create;
        JPEGImage.SaveToStream(fs);
        Table1.Append;
        Table1.FieldByName('題目ID').AsInteger:=Table1.RecordCount+1;
        TBlobField(Table1.FieldByName('圖片')).LoadFromFile(OpenpictureDialog1.Filename);
        Table1.Post;
        finally
        fs.Free;
        end;
        FINALLY
        JPEGImage.Free
        END;
    END;    END;
end;
procedure TForm2.DBGrid1DblClick(Sender: TObject);
var
bS : TBlobStream;
Pic : TJpegImage;
begin
    if DBGrid1.SelectedField = TDBGrid(Sender).DataSource.DataSet.FieldByName('圖片') then
    bS := TBlobStream.Create(TBlobField(Table1.FieldByName('圖片')), bmWrite); // AdoTable1Picture為欄位名稱。
    try
        Pic:=TJpegImage.Create;
        try
        Pic.LoadFromStream(bS);
        Image1.Picture.Assign(Pic);
    finally
        Pic.Free;
    end;
    finally
    bS.Free
    end;
end;end.

Answer 6

DECLARE @buyVariable INTSELECT @buyVariable = SUM(buyprice)
FROM GetEqu
GO
CREATE VIEW DeptReport 
AS 
SELECT         
DName, 
count(eid) as qty,
SUM(buyprice) AS Price,  
COUNT(eid)/COUNT(eid)* 100 AS QtyPer,SUM(buyprice)/ (CASE WHEN buyVariable= 0 THEN 1 ELSE buyVariable) END) * 100 AS PricePer 
FROM  GetEqu a GROUP BY  DName